[Home] [Help]
PACKAGE BODY: APPS.OKC_ROLE_SOURCE_PUB
Source
1 PACKAGE BODY OKC_ROLE_SOURCE_PUB AS
2 /* $Header: OKCPRSCB.pls 120.0 2005/05/26 09:51:09 appldev noship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4
5 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
6 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_ROLE_SOURCES_PUB';
7 G_TOO_MANY_SOURCES CONSTANT VARCHAR2(20) := 'OKC_TOO_MANY_SOURCES';
8
9 procedure reset(p_rscv_rec IN rscv_rec_type) is
10 begin
11 g_rscv_rec.RLE_CODE := p_rscv_rec.RLE_CODE;
12 g_rscv_rec.BUY_OR_SELL := p_rscv_rec.BUY_OR_SELL;
13 g_rscv_rec.START_DATE := p_rscv_rec.START_DATE;
14 g_rscv_rec.object_version_number := p_rscv_rec.object_version_number;
15 g_rscv_rec.created_by := p_rscv_rec.created_by;
16 g_rscv_rec.creation_date := p_rscv_rec.creation_date;
17 g_rscv_rec.last_updated_by := p_rscv_rec.last_updated_by;
18 g_rscv_rec.last_update_date := p_rscv_rec.last_update_date;
19 g_rscv_rec.last_update_login := p_rscv_rec.last_update_login;
20 g_rscv_rec.access_level := p_rscv_rec.access_level;
21 end reset;
22
23 procedure reset(p_csov_rec IN csov_rec_type) is
24 begin
25 g_csov_rec.RLE_CODE := p_csov_rec.RLE_CODE;
26 g_csov_rec.CRO_CODE := p_csov_rec.CRO_CODE;
27 g_csov_rec.BUY_OR_SELL := p_csov_rec.BUY_OR_SELL;
28 g_csov_rec.START_DATE := p_csov_rec.START_DATE;
29 g_csov_rec.object_version_number := p_csov_rec.object_version_number;
30 g_csov_rec.created_by := p_csov_rec.created_by;
31 g_csov_rec.creation_date := p_csov_rec.creation_date;
32 g_csov_rec.last_updated_by := p_csov_rec.last_updated_by;
33 g_csov_rec.last_update_date := p_csov_rec.last_update_date;
34 g_csov_rec.last_update_login := p_csov_rec.last_update_login;
35 g_csov_rec.access_level := p_csov_rec.access_level;
36 end reset;
37
38 -- Start of comments
39 --
40 -- Procedure Name : contact_role_meaning
41 -- Description :
42 -- Business Rules :
43 -- Parameters :
44 -- Version : 1.0
45 -- End of comments
46
47 function contact_role_meaning(p_cro_code in varchar2) return varchar2 is
48 cursor c1 is
49 select meaning
50 from fnd_lookups
51 where lookup_type = 'OKC_CONTACT_ROLE'
52 and lookup_code = p_cro_code;
53 v1 varchar2(300);
54 begin
55 open c1;
56 fetch c1 into v1;
57 close c1;
58 return v1;
59 end contact_role_meaning;
60
61 -- Start of comments
62 --
63 -- Procedure Name : source_name
64 -- Description :
65 -- Business Rules :
66 -- Parameters :
67 -- Version : 1.0
68 -- End of comments
69
70 function source_name(p_jtot_object_code in varchar2) return varchar2 is
71 cursor c1 is
72 select name
73 from jtf_objects_vl
74 where object_code = p_jtot_object_code;
75 v1 varchar2(300);
76 begin
77 open c1;
78 fetch c1 into v1;
79 close c1;
80 return v1;
81 end source_name;
82
83 -- Start of comments
84 --
85 -- Procedure Name : one_role_source_atime
86 -- Description :
87 -- Business Rules :
88 -- Parameters :
89 -- Version : 1.0
90 -- End of comments
91
92 procedure one_role_source_atime(x_return_status OUT NOCOPY VARCHAR2,
93 p_rle_code in varchar2) is
94
95 -- Changed the select statement with a UNION ALL for performance reasons 08/14/2000
96 cursor c1 is
97 select START_DATE+0.5
98 from okc_role_sources
99 where RLE_CODE = p_rle_code AND p_rle_code is not null
100 and nvl(end_date,sysdate)>=sysdate
101 UNION ALL
102 select START_DATE+0.5
103 from okc_role_sources
104 where p_rle_code is null
105 and nvl(end_date,sysdate)>=sysdate;
106 d1 date;
107 cursor c2 is
108 -- select max(count(start_date))
109 -- from okc_role_sources
110 -- where RLE_CODE = NVL(p_rle_code,RLE_CODE)
111 -- and d1 between start_date and NVL(end_date,d1)
112 -- group by RLE_CODE, buy_or_sell;
113 select count(start_date)
114 from okc_role_sources
115 where RLE_CODE = p_rle_code AND p_rle_code is not null
116 and d1 between start_date and NVL(end_date,d1)
117 group by RLE_CODE, buy_or_sell
118 having count(start_date) > 1
119 UNION ALL
120 select count(start_date)
121 from okc_role_sources
122 where p_rle_code is null
123 and d1 between start_date and NVL(end_date,d1)
124 group by RLE_CODE, buy_or_sell
125 having count(start_date) > 1;
126 n1 number;
127 begin
128 x_return_status := OKC_API.G_RET_STS_SUCCESS;
129 open c1;
130 loop
131 fetch c1 into d1;
132 exit when c1%NOTFOUND;
133 open c2;
134 fetch c2 into n1;
135 close c2;
136 if (n1>1) then
137 FND_MESSAGE.SET_NAME(application => G_APP_NAME,
138 name => G_TOO_MANY_SOURCES);
139 FND_MESSAGE.SET_TOKEN(token => 'DATE',
140 value => fnd_date.date_to_chardate(d1));
141 FND_MESSAGE.SET_TOKEN(token => 'SOURCE',
142 value => 'OKC_ROLE_SOURCES',
143 translate => TRUE);
144 FND_MSG_PUB.add;
145 x_return_status := OKC_API.G_RET_STS_ERROR;
146 exit;
147 end if;
148 end loop;
149 close c1;
150 end one_role_source_atime;
151
152 -- Start of comments
153 --
154 -- Procedure Name : one_contact_source_atime
155 -- Description :
156 -- Business Rules :
157 -- Parameters :
158 -- Version : 1.0
159 -- End of comments
160
161 procedure one_contact_source_atime(x_return_status OUT NOCOPY VARCHAR2,
162 p_rle_code in varchar2) is
163
164 -- Changed the select statement to UNION ALL for performance reasons 08/14/2000
165
166 cursor c1 is
167 select START_DATE+0.5
168 from okc_contact_sources
169 where RLE_CODE = p_rle_code AND p_rle_code is not null
170 and nvl(end_date,sysdate)>=sysdate
171 UNION ALL
172 select START_DATE+0.5
173 from okc_contact_sources
174 where p_rle_code is null
175 and nvl(end_date,sysdate)>=sysdate;
176 d1 date;
177 cursor c2 is
178 -- select max(count(start_date))
179 -- from okc_contact_sources
180 -- where RLE_CODE = NVL(p_rle_code,RLE_CODE)
181 -- and d1 between start_date and NVL(end_date,d1)
182 -- group by RLE_CODE, CRO_CODE, buy_or_sell;
183 select count(start_date)
184 from okc_contact_sources
185 where RLE_CODE = p_rle_code and p_rle_code is not null
186 and d1 between start_date and NVL(end_date,d1)
187 group by RLE_CODE, CRO_CODE, buy_or_sell
188 having count(start_date) > 1
189 UNION ALL
190 select count(start_date)
191 from okc_contact_sources
192 where p_rle_code is null
193 and d1 between start_date and NVL(end_date,d1)
194 group by RLE_CODE, CRO_CODE, buy_or_sell
195 having count(start_date) > 1;
196 n1 number;
197 begin
198 x_return_status := OKC_API.G_RET_STS_SUCCESS;
199 open c1;
200 loop
201 fetch c1 into d1;
202 exit when c1%NOTFOUND;
203 open c2;
204 fetch c2 into n1;
205 close c2;
206 if (n1>1) then
207 FND_MESSAGE.SET_NAME(application => G_APP_NAME,
208 name => G_TOO_MANY_SOURCES);
209 FND_MESSAGE.SET_TOKEN(token => 'DATE',
210 value => fnd_date.date_to_chardate(d1));
211 FND_MESSAGE.SET_TOKEN(token => 'SOURCE',
212 value => 'OKC_CONTACT_SOURCES',
213 translate => TRUE);
214 FND_MSG_PUB.add;
215 x_return_status := OKC_API.G_RET_STS_ERROR;
216 exit;
217 end if;
218 end loop;
219 close c1;
220 end one_contact_source_atime;
221
222 -- Start of comments
223 --
224 -- Procedure Name : create_role_source
225 -- Description :
226 -- Business Rules :
227 -- Parameters :
228 -- Version : 1.0
229 -- End of comments
230
231 procedure create_role_source(p_api_version IN NUMBER,
232 p_init_msg_list IN VARCHAR2 ,
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY NUMBER,
235 x_msg_data OUT NOCOPY VARCHAR2,
236 p_rscv_rec IN rscv_rec_type,
237 x_rscv_rec OUT NOCOPY rscv_rec_type) is
238 l_api_name CONSTANT VARCHAR2(30) := 'create_role_source';
239 l_api_version CONSTANT NUMBER := 1;
240 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
241 begin
242 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
243 G_PKG_NAME,
244 p_init_msg_list,
245 l_api_version,
246 p_api_version,
247 '_PUB',
248 x_return_status);
249 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
250 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
251 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
252 RAISE OKC_API.G_EXCEPTION_ERROR;
253 END IF;
254 --
255 -- Call Before Logic Hook
256 --
257 g_rscv_rec := p_rscv_rec;
258 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
259 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
260 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
261 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
262 raise OKC_API.G_EXCEPTION_ERROR;
263 END IF;
264 reset(p_rscv_rec);
265 OKC_ROLE_SOURCE_PVT.create_role_source(p_api_version,
266 p_init_msg_list,
267 x_return_status,
268 x_msg_count,
269 x_msg_data,
270 g_rscv_rec,
271 x_rscv_rec);
272 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
273 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
274 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
275 RAISE OKC_API.G_EXCEPTION_ERROR;
276 END IF;
277 --
278 -- Call After Logic Hook
279 --
280 g_rscv_rec := x_rscv_rec;
281 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
282 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
283 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
284 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
285 raise OKC_API.G_EXCEPTION_ERROR;
286 END IF;
287 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
288 EXCEPTION
289 WHEN OKC_API.G_EXCEPTION_ERROR THEN
290 x_return_status := OKC_API.HANDLE_EXCEPTIONS
291 (substr(l_api_name,1,26),
292 G_PKG_NAME,
293 'OKC_API.G_RET_STS_ERROR',
294 x_msg_count,
295 x_msg_data,
296 '_PUB');
297 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
298 x_return_status := OKC_API.HANDLE_EXCEPTIONS
299 (substr(l_api_name,1,26),
300 G_PKG_NAME,
301 'OKC_API.G_RET_STS_UNEXP_ERROR',
302 x_msg_count,
303 x_msg_data,
304 '_PUB');
305 WHEN OTHERS THEN
306 x_return_status := OKC_API.HANDLE_EXCEPTIONS
307 (substr(l_api_name,1,26),
308 G_PKG_NAME,
309 'OTHERS',
310 x_msg_count,
311 x_msg_data,
312 '_PUB');
313 end create_role_source;
314
315 -- Start of comments
316 --
317 -- Procedure Name : create_role_source
318 -- Description :
319 -- Business Rules :
320 -- Parameters :
321 -- Version : 1.0
322 -- End of comments
323
324 procedure create_role_source(p_api_version IN NUMBER,
325 p_init_msg_list IN VARCHAR2 ,
326 x_return_status OUT NOCOPY VARCHAR2,
327 x_msg_count OUT NOCOPY NUMBER,
328 x_msg_data OUT NOCOPY VARCHAR2,
329 p_rscv_tbl IN rscv_tbl_type,
330 x_rscv_tbl OUT NOCOPY rscv_tbl_type) is
331 i NUMBER;
332 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
333 begin
334 OKC_API.init_msg_list(p_init_msg_list);
335 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
336 if (p_rscv_tbl.COUNT>0) then
337 i := p_rscv_tbl.FIRST;
338 LOOP
339 create_role_source(p_api_version=>p_api_version,
340 p_init_msg_list=>OKC_API.G_FALSE,
341 x_return_status=>l_return_status,
342 x_msg_count=>x_msg_count,
343 x_msg_data=>x_msg_data,
344 p_rscv_rec=>p_rscv_tbl(i),
345 x_rscv_rec=>x_rscv_tbl(i));
346 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
347 x_return_status := l_return_status;
348 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
349 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
350 x_return_status := l_return_status;
351 end if;
352 EXIT WHEN (i=p_rscv_tbl.LAST);
353 i := p_rscv_tbl.NEXT(i);
354 END LOOP;
355 end if;
356 exception
357 when others then NULL;
358 end create_role_source;
359
360 procedure update_role_source(p_api_version IN NUMBER,
361 p_init_msg_list IN VARCHAR2 ,
362 x_return_status OUT NOCOPY VARCHAR2,
363 x_msg_count OUT NOCOPY NUMBER,
364 x_msg_data OUT NOCOPY VARCHAR2,
365 p_rscv_rec IN rscv_rec_type,
366 x_rscv_rec OUT NOCOPY rscv_rec_type) is
367 l_api_name CONSTANT VARCHAR2(30) := 'update_role_source';
368 l_api_version CONSTANT NUMBER := 1;
369 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
370 begin
371 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
372 G_PKG_NAME,
373 p_init_msg_list,
374 l_api_version,
375 p_api_version,
376 '_PUB',
377 x_return_status);
378 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
379 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
380 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
381 RAISE OKC_API.G_EXCEPTION_ERROR;
382 END IF;
383 --
384 -- Call Before Logic Hook
385 --
386 g_rscv_rec := p_rscv_rec;
387 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
388 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
389 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
390 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
391 raise OKC_API.G_EXCEPTION_ERROR;
392 END IF;
393 reset(p_rscv_rec);
394 OKC_ROLE_SOURCE_PVT.update_role_source(p_api_version,
395 p_init_msg_list,
396 x_return_status,
397 x_msg_count,
398 x_msg_data,
399 g_rscv_rec,
400 x_rscv_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 g_rscv_rec := x_rscv_rec;
410 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
411 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
412 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
413 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
414 raise OKC_API.G_EXCEPTION_ERROR;
415 END IF;
416 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
417 EXCEPTION
418 WHEN OKC_API.G_EXCEPTION_ERROR THEN
419 x_return_status := OKC_API.HANDLE_EXCEPTIONS
420 (substr(l_api_name,1,26),
421 G_PKG_NAME,
422 'OKC_API.G_RET_STS_ERROR',
423 x_msg_count,
424 x_msg_data,
425 '_PUB');
426 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
427 x_return_status := OKC_API.HANDLE_EXCEPTIONS
428 (substr(l_api_name,1,26),
429 G_PKG_NAME,
430 'OKC_API.G_RET_STS_UNEXP_ERROR',
431 x_msg_count,
432 x_msg_data,
433 '_PUB');
434 WHEN OTHERS THEN
435 x_return_status := OKC_API.HANDLE_EXCEPTIONS
436 (substr(l_api_name,1,26),
437 G_PKG_NAME,
438 'OTHERS',
439 x_msg_count,
440 x_msg_data,
441 '_PUB');
442 end update_role_source;
443
444 -- Start of comments
445 --
446 -- Procedure Name : update_role_source
447 -- Description :
448 -- Business Rules :
449 -- Parameters :
450 -- Version : 1.0
451 -- End of comments
452
453 procedure update_role_source(p_api_version IN NUMBER,
454 p_init_msg_list IN VARCHAR2 ,
455 x_return_status OUT NOCOPY VARCHAR2,
456 x_msg_count OUT NOCOPY NUMBER,
457 x_msg_data OUT NOCOPY VARCHAR2,
458 p_rscv_tbl IN rscv_tbl_type,
459 x_rscv_tbl OUT NOCOPY rscv_tbl_type) is
460 i NUMBER;
461 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
462 begin
463 OKC_API.init_msg_list(p_init_msg_list);
464 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
465 if (p_rscv_tbl.COUNT>0) then
466 i := p_rscv_tbl.FIRST;
467 LOOP
468 update_role_source(p_api_version=>p_api_version,
469 p_init_msg_list=>OKC_API.G_FALSE,
470 x_return_status=>l_return_status,
471 x_msg_count=>x_msg_count,
472 x_msg_data=>x_msg_data,
473 p_rscv_rec=>p_rscv_tbl(i),
474 x_rscv_rec=>x_rscv_tbl(i));
475 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
476 x_return_status := l_return_status;
477 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
478 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
479 x_return_status := l_return_status;
480 end if;
481 EXIT WHEN (i=p_rscv_tbl.LAST);
482 i := p_rscv_tbl.NEXT(i);
483 END LOOP;
484 end if;
485 exception
486 when others then NULL;
487 end update_role_source;
488
489 -- Start of comments
490 --
491 -- Procedure Name : lock_role_source
492 -- Description :
493 -- Business Rules :
494 -- Parameters :
495 -- Version : 1.0
496 -- End of comments
497
498 procedure lock_role_source(p_api_version IN NUMBER,
499 p_init_msg_list IN VARCHAR2 ,
500 x_return_status OUT NOCOPY VARCHAR2,
501 x_msg_count OUT NOCOPY NUMBER,
502 x_msg_data OUT NOCOPY VARCHAR2,
503 p_rscv_rec IN rscv_rec_type) is
504 l_api_name CONSTANT VARCHAR2(30) := 'lock_role_source';
505 l_api_version CONSTANT NUMBER := 1;
506 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
507 begin
508 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
509 G_PKG_NAME,
510 p_init_msg_list,
511 l_api_version,
512 p_api_version,
513 '_PUB',
514 x_return_status);
515 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
516 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
517 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
518 RAISE OKC_API.G_EXCEPTION_ERROR;
519 END IF;
520 --
521 -- Call Before Logic Hook
522 --
523 g_rscv_rec := p_rscv_rec;
524 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
525 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
526 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
527 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
528 raise OKC_API.G_EXCEPTION_ERROR;
529 END IF;
530 reset(p_rscv_rec);
531 OKC_ROLE_SOURCE_PVT.lock_role_source(p_api_version,
532 p_init_msg_list,
533 x_return_status,
534 x_msg_count,
535 x_msg_data,
536 g_rscv_rec);
537 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
538 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
539 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
540 RAISE OKC_API.G_EXCEPTION_ERROR;
541 END IF;
542 --
543 -- Call After Logic Hook
544 --
545 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
546 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
547 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
548 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
549 raise OKC_API.G_EXCEPTION_ERROR;
550 END IF;
551 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
552 EXCEPTION
553 WHEN OKC_API.G_EXCEPTION_ERROR THEN
554 x_return_status := OKC_API.HANDLE_EXCEPTIONS
555 (substr(l_api_name,1,26),
556 G_PKG_NAME,
557 'OKC_API.G_RET_STS_ERROR',
558 x_msg_count,
559 x_msg_data,
560 '_PUB');
561 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
562 x_return_status := OKC_API.HANDLE_EXCEPTIONS
563 (substr(l_api_name,1,26),
564 G_PKG_NAME,
565 'OKC_API.G_RET_STS_UNEXP_ERROR',
566 x_msg_count,
567 x_msg_data,
568 '_PUB');
569 WHEN OTHERS THEN
570 x_return_status := OKC_API.HANDLE_EXCEPTIONS
571 (substr(l_api_name,1,26),
572 G_PKG_NAME,
573 'OTHERS',
574 x_msg_count,
575 x_msg_data,
576 '_PUB');
577 end lock_role_source;
578
579 -- Start of comments
580 --
581 -- Procedure Name : lock_role_source
582 -- Description :
583 -- Business Rules :
584 -- Parameters :
585 -- Version : 1.0
586 -- End of comments
587
588 procedure lock_role_source(p_api_version IN NUMBER,
589 p_init_msg_list IN VARCHAR2 ,
590 x_return_status OUT NOCOPY VARCHAR2,
591 x_msg_count OUT NOCOPY NUMBER,
592 x_msg_data OUT NOCOPY VARCHAR2,
593 p_rscv_tbl IN rscv_tbl_type) is
594 i NUMBER;
595 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
596 begin
597 OKC_API.init_msg_list(p_init_msg_list);
598 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
599 if (p_rscv_tbl.COUNT>0) then
600 i := p_rscv_tbl.FIRST;
601 LOOP
602 lock_role_source(p_api_version=>p_api_version,
603 p_init_msg_list=>OKC_API.G_FALSE,
604 x_return_status=>l_return_status,
605 x_msg_count=>x_msg_count,
606 x_msg_data=>x_msg_data,
607 p_rscv_rec=>p_rscv_tbl(i));
608 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
609 x_return_status := l_return_status;
610 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
611 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
612 x_return_status := l_return_status;
613 end if;
614 EXIT WHEN (i=p_rscv_tbl.LAST);
615 i := p_rscv_tbl.NEXT(i);
616 END LOOP;
617 end if;
618 exception
619 when others then NULL;
620 end lock_role_source;
621
622 -- Start of comments
623 --
624 -- Procedure Name : validate_role_source
625 -- Description :
626 -- Business Rules :
627 -- Parameters :
628 -- Version : 1.0
629 -- End of comments
630
631 procedure validate_role_source(p_api_version IN NUMBER,
632 p_init_msg_list IN VARCHAR2 ,
633 x_return_status OUT NOCOPY VARCHAR2,
634 x_msg_count OUT NOCOPY NUMBER,
635 x_msg_data OUT NOCOPY VARCHAR2,
636 p_rscv_rec IN rscv_rec_type) is
637 l_api_name CONSTANT VARCHAR2(30) := 'validate_role_source';
638 l_api_version CONSTANT NUMBER := 1;
639 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
640 begin
641 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
642 G_PKG_NAME,
643 p_init_msg_list,
644 l_api_version,
645 p_api_version,
646 '_PUB',
647 x_return_status);
648 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
649 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
650 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
651 RAISE OKC_API.G_EXCEPTION_ERROR;
652 END IF;
653 --
654 -- Call Before Logic Hook
655 --
656 g_rscv_rec := p_rscv_rec;
657 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
658 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
659 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
660 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
661 raise OKC_API.G_EXCEPTION_ERROR;
662 END IF;
663 reset(p_rscv_rec);
664 OKC_ROLE_SOURCE_PVT.validate_role_source(p_api_version,
665 p_init_msg_list,
666 x_return_status,
667 x_msg_count,
668 x_msg_data,
669 g_rscv_rec);
670 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
671 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
672 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
673 RAISE OKC_API.G_EXCEPTION_ERROR;
674 END IF;
675 --
676 -- Call After Logic Hook
677 --
678 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
679 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
680 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
681 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
682 raise OKC_API.G_EXCEPTION_ERROR;
683 END IF;
684 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
685 EXCEPTION
686 WHEN OKC_API.G_EXCEPTION_ERROR THEN
687 x_return_status := OKC_API.HANDLE_EXCEPTIONS
688 (substr(l_api_name,1,26),
689 G_PKG_NAME,
690 'OKC_API.G_RET_STS_ERROR',
691 x_msg_count,
692 x_msg_data,
693 '_PUB');
694 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
695 x_return_status := OKC_API.HANDLE_EXCEPTIONS
696 (substr(l_api_name,1,26),
697 G_PKG_NAME,
698 'OKC_API.G_RET_STS_UNEXP_ERROR',
699 x_msg_count,
700 x_msg_data,
701 '_PUB');
702 WHEN OTHERS THEN
703 x_return_status := OKC_API.HANDLE_EXCEPTIONS
704 (substr(l_api_name,1,26),
705 G_PKG_NAME,
706 'OTHERS',
707 x_msg_count,
708 x_msg_data,
709 '_PUB');
710 end validate_role_source;
711
712 -- Start of comments
713 --
714 -- Procedure Name : validate_role_source
715 -- Description :
716 -- Business Rules :
717 -- Parameters :
718 -- Version : 1.0
719 -- End of comments
720
721 procedure validate_role_source(p_api_version IN NUMBER,
722 p_init_msg_list IN VARCHAR2 ,
723 x_return_status OUT NOCOPY VARCHAR2,
724 x_msg_count OUT NOCOPY NUMBER,
725 x_msg_data OUT NOCOPY VARCHAR2,
726 p_rscv_tbl IN rscv_tbl_type) is
727 i NUMBER;
728 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
729 begin
730 OKC_API.init_msg_list(p_init_msg_list);
731 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
732 if (p_rscv_tbl.COUNT>0) then
733 i := p_rscv_tbl.FIRST;
734 LOOP
735 validate_role_source(p_api_version=>p_api_version,
736 p_init_msg_list=>OKC_API.G_FALSE,
737 x_return_status=>l_return_status,
738 x_msg_count=>x_msg_count,
739 x_msg_data=>x_msg_data,
740 p_rscv_rec=>p_rscv_tbl(i));
741 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
742 x_return_status := l_return_status;
743 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
744 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
745 x_return_status := l_return_status;
746 end if;
747 EXIT WHEN (i=p_rscv_tbl.LAST);
748 i := p_rscv_tbl.NEXT(i);
749 END LOOP;
750 end if;
751 exception
752 when others then NULL;
753 end validate_role_source;
754
755 -- Start of comments
756 --
757 -- Procedure Name : create_contact_source
758 -- Description :
759 -- Business Rules :
760 -- Parameters :
761 -- Version : 1.0
762 -- End of comments
763
764 procedure create_contact_source(p_api_version IN NUMBER,
765 p_init_msg_list IN VARCHAR2 ,
766 x_return_status OUT NOCOPY VARCHAR2,
767 x_msg_count OUT NOCOPY NUMBER,
768 x_msg_data OUT NOCOPY VARCHAR2,
769 p_csov_rec IN csov_rec_type,
770 x_csov_rec OUT NOCOPY csov_rec_type) is
771 l_api_name CONSTANT VARCHAR2(30) := 'create_contact_source';
772 l_api_version CONSTANT NUMBER := 1;
773 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
774 begin
775 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
776 G_PKG_NAME,
777 p_init_msg_list,
778 l_api_version,
779 p_api_version,
780 '_PUB',
781 x_return_status);
782 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
783 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
784 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
785 RAISE OKC_API.G_EXCEPTION_ERROR;
786 END IF;
787 --
788 -- Call Before Logic Hook
789 --
790 g_csov_rec := p_csov_rec;
791 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
792 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
793 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
794 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
795 raise OKC_API.G_EXCEPTION_ERROR;
796 END IF;
797 reset(p_csov_rec);
798 OKC_ROLE_SOURCE_PVT.create_contact_source(p_api_version,
799 p_init_msg_list,
800 x_return_status,
801 x_msg_count,
802 x_msg_data,
803 g_csov_rec,
804 x_csov_rec);
805 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
806 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
807 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
808 RAISE OKC_API.G_EXCEPTION_ERROR;
809 END IF;
810 --
811 -- Call After Logic Hook
812 --
813 g_csov_rec := x_csov_rec;
814 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
815 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
816 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
817 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
818 raise OKC_API.G_EXCEPTION_ERROR;
819 END IF;
820 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
821 EXCEPTION
822 WHEN OKC_API.G_EXCEPTION_ERROR THEN
823 x_return_status := OKC_API.HANDLE_EXCEPTIONS
824 (substr(l_api_name,1,26),
825 G_PKG_NAME,
826 'OKC_API.G_RET_STS_ERROR',
827 x_msg_count,
828 x_msg_data,
829 '_PUB');
830 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
831 x_return_status := OKC_API.HANDLE_EXCEPTIONS
832 (substr(l_api_name,1,26),
833 G_PKG_NAME,
834 'OKC_API.G_RET_STS_UNEXP_ERROR',
835 x_msg_count,
836 x_msg_data,
837 '_PUB');
838 WHEN OTHERS THEN
839 x_return_status := OKC_API.HANDLE_EXCEPTIONS
840 (substr(l_api_name,1,26),
841 G_PKG_NAME,
842 'OTHERS',
843 x_msg_count,
844 x_msg_data,
845 '_PUB');
846 end create_contact_source;
847
848 -- Start of comments
849 --
850 -- Procedure Name : create_contact_source
851 -- Description :
852 -- Business Rules :
853 -- Parameters :
854 -- Version : 1.0
855 -- End of comments
856
857 procedure create_contact_source(p_api_version IN NUMBER,
858 p_init_msg_list IN VARCHAR2 ,
859 x_return_status OUT NOCOPY VARCHAR2,
860 x_msg_count OUT NOCOPY NUMBER,
861 x_msg_data OUT NOCOPY VARCHAR2,
862 p_csov_tbl IN csov_tbl_type,
863 x_csov_tbl OUT NOCOPY csov_tbl_type) is
864 i NUMBER;
865 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
866 begin
867 OKC_API.init_msg_list(p_init_msg_list);
868 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
869 if (p_csov_tbl.COUNT>0) then
870 i := p_csov_tbl.FIRST;
871 LOOP
872 create_contact_source(p_api_version=>p_api_version,
873 p_init_msg_list=>OKC_API.G_FALSE,
874 x_return_status=>l_return_status,
875 x_msg_count=>x_msg_count,
876 x_msg_data=>x_msg_data,
877 p_csov_rec=>p_csov_tbl(i),
878 x_csov_rec=>x_csov_tbl(i));
879 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
880 x_return_status := l_return_status;
881 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
882 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
883 x_return_status := l_return_status;
884 end if;
885 EXIT WHEN (i=p_csov_tbl.LAST);
886 i := p_csov_tbl.NEXT(i);
887 END LOOP;
888 end if;
889 exception
890 when others then NULL;
891 end create_contact_source;
892
893 -- Start of comments
894 --
895 -- Procedure Name : update_contact_source
896 -- Description :
897 -- Business Rules :
898 -- Parameters :
899 -- Version : 1.0
900 -- End of comments
901
902 procedure update_contact_source(p_api_version IN NUMBER,
903 p_init_msg_list IN VARCHAR2 ,
904 x_return_status OUT NOCOPY VARCHAR2,
905 x_msg_count OUT NOCOPY NUMBER,
906 x_msg_data OUT NOCOPY VARCHAR2,
907 p_csov_rec IN csov_rec_type,
908 x_csov_rec OUT NOCOPY csov_rec_type) is
909 l_api_name CONSTANT VARCHAR2(30) := 'update_contact_source';
910 l_api_version CONSTANT NUMBER := 1;
911 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
912 begin
913 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
914 G_PKG_NAME,
915 p_init_msg_list,
916 l_api_version,
917 p_api_version,
918 '_PUB',
919 x_return_status);
920 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
921 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
922 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
923 RAISE OKC_API.G_EXCEPTION_ERROR;
924 END IF;
925 --
926 -- Call Before Logic Hook
927 --
928 g_csov_rec := p_csov_rec;
929 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
930 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
931 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
932 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
933 raise OKC_API.G_EXCEPTION_ERROR;
934 END IF;
935 reset(p_csov_rec);
936 OKC_ROLE_SOURCE_PVT.update_contact_source(p_api_version,
937 p_init_msg_list,
938 x_return_status,
939 x_msg_count,
940 x_msg_data,
941 g_csov_rec,
942 x_csov_rec);
943 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
944 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
945 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
946 RAISE OKC_API.G_EXCEPTION_ERROR;
947 END IF;
948 --
949 -- Call After Logic Hook
950 --
951 g_csov_rec := x_csov_rec;
952 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
953 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
954 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
955 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
956 raise OKC_API.G_EXCEPTION_ERROR;
957 END IF;
958 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
959 EXCEPTION
960 WHEN OKC_API.G_EXCEPTION_ERROR THEN
961 x_return_status := OKC_API.HANDLE_EXCEPTIONS
962 (substr(l_api_name,1,26),
963 G_PKG_NAME,
964 'OKC_API.G_RET_STS_ERROR',
965 x_msg_count,
966 x_msg_data,
967 '_PUB');
968 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
969 x_return_status := OKC_API.HANDLE_EXCEPTIONS
970 (substr(l_api_name,1,26),
971 G_PKG_NAME,
972 'OKC_API.G_RET_STS_UNEXP_ERROR',
973 x_msg_count,
974 x_msg_data,
975 '_PUB');
976 WHEN OTHERS THEN
977 x_return_status := OKC_API.HANDLE_EXCEPTIONS
978 (substr(l_api_name,1,26),
979 G_PKG_NAME,
980 'OTHERS',
981 x_msg_count,
982 x_msg_data,
983 '_PUB');
984 end update_contact_source;
985
986 -- Start of comments
987 --
988 -- Procedure Name : update_contact_source
989 -- Description :
990 -- Business Rules :
991 -- Parameters :
992 -- Version : 1.0
993 -- End of comments
994
995 procedure update_contact_source(p_api_version IN NUMBER,
996 p_init_msg_list IN VARCHAR2 ,
997 x_return_status OUT NOCOPY VARCHAR2,
998 x_msg_count OUT NOCOPY NUMBER,
999 x_msg_data OUT NOCOPY VARCHAR2,
1000 p_csov_tbl IN csov_tbl_type,
1001 x_csov_tbl OUT NOCOPY csov_tbl_type) is
1002 i NUMBER;
1003 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1004 begin
1005 OKC_API.init_msg_list(p_init_msg_list);
1006 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
1007 if (p_csov_tbl.COUNT>0) then
1008 i := p_csov_tbl.FIRST;
1009 LOOP
1010 update_contact_source(p_api_version=>p_api_version,
1011 p_init_msg_list=>OKC_API.G_FALSE,
1012 x_return_status=>l_return_status,
1013 x_msg_count=>x_msg_count,
1014 x_msg_data=>x_msg_data,
1015 p_csov_rec=>p_csov_tbl(i),
1016 x_csov_rec=>x_csov_tbl(i));
1017 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1018 x_return_status := l_return_status;
1019 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1020 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
1021 x_return_status := l_return_status;
1022 end if;
1023 EXIT WHEN (i=p_csov_tbl.LAST);
1024 i := p_csov_tbl.NEXT(i);
1025 END LOOP;
1026 end if;
1027 exception
1028 when others then NULL;
1029 end update_contact_source;
1030
1031 -- Start of comments
1032 --
1033 -- Procedure Name : lock_contact_source
1034 -- Description :
1035 -- Business Rules :
1036 -- Parameters :
1037 -- Version : 1.0
1038 -- End of comments
1039
1040 procedure lock_contact_source(p_api_version IN NUMBER,
1041 p_init_msg_list IN VARCHAR2 ,
1042 x_return_status OUT NOCOPY VARCHAR2,
1043 x_msg_count OUT NOCOPY NUMBER,
1044 x_msg_data OUT NOCOPY VARCHAR2,
1045 p_csov_rec IN csov_rec_type) is
1046 l_api_name CONSTANT VARCHAR2(30) := 'lock_contact_source';
1047 l_api_version CONSTANT NUMBER := 1;
1048 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1049 begin
1050 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
1051 G_PKG_NAME,
1052 p_init_msg_list,
1053 l_api_version,
1054 p_api_version,
1055 '_PUB',
1056 x_return_status);
1057 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1058 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1059 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1060 RAISE OKC_API.G_EXCEPTION_ERROR;
1061 END IF;
1062 --
1063 -- Call Before Logic Hook
1064 --
1065 g_csov_rec := p_csov_rec;
1066 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
1067 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1068 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1069 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1070 raise OKC_API.G_EXCEPTION_ERROR;
1071 END IF;
1072 reset(p_csov_rec);
1073 OKC_ROLE_SOURCE_PVT.lock_contact_source(p_api_version,
1074 p_init_msg_list,
1075 x_return_status,
1076 x_msg_count,
1077 x_msg_data,
1078 g_csov_rec);
1079 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1080 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1081 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1082 RAISE OKC_API.G_EXCEPTION_ERROR;
1083 END IF;
1084 --
1085 -- Call After Logic Hook
1086 --
1087 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
1088 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1089 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1090 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1091 raise OKC_API.G_EXCEPTION_ERROR;
1092 END IF;
1093 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1094 EXCEPTION
1095 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1096 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1097 (substr(l_api_name,1,26),
1098 G_PKG_NAME,
1099 'OKC_API.G_RET_STS_ERROR',
1100 x_msg_count,
1101 x_msg_data,
1102 '_PUB');
1103 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1104 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1105 (substr(l_api_name,1,26),
1106 G_PKG_NAME,
1107 'OKC_API.G_RET_STS_UNEXP_ERROR',
1108 x_msg_count,
1109 x_msg_data,
1110 '_PUB');
1111 WHEN OTHERS THEN
1112 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1113 (substr(l_api_name,1,26),
1114 G_PKG_NAME,
1115 'OTHERS',
1116 x_msg_count,
1117 x_msg_data,
1118 '_PUB');
1119 end lock_contact_source;
1120
1121 -- Start of comments
1122 --
1123 -- Procedure Name : lock_contact_source
1124 -- Description :
1125 -- Business Rules :
1126 -- Parameters :
1127 -- Version : 1.0
1128 -- End of comments
1129
1130 procedure lock_contact_source(p_api_version IN NUMBER,
1131 p_init_msg_list IN VARCHAR2 ,
1132 x_return_status OUT NOCOPY VARCHAR2,
1133 x_msg_count OUT NOCOPY NUMBER,
1134 x_msg_data OUT NOCOPY VARCHAR2,
1135 p_csov_tbl IN csov_tbl_type) is
1136 i NUMBER;
1137 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1138 begin
1139 OKC_API.init_msg_list(p_init_msg_list);
1140 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
1141 if (p_csov_tbl.COUNT>0) then
1142 i := p_csov_tbl.FIRST;
1143 LOOP
1144 lock_contact_source(p_api_version=>p_api_version,
1145 p_init_msg_list=>OKC_API.G_FALSE,
1146 x_return_status=>l_return_status,
1147 x_msg_count=>x_msg_count,
1148 x_msg_data=>x_msg_data,
1149 p_csov_rec=>p_csov_tbl(i));
1150 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1151 x_return_status := l_return_status;
1152 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1153 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
1154 x_return_status := l_return_status;
1155 end if;
1156 EXIT WHEN (i=p_csov_tbl.LAST);
1157 i := p_csov_tbl.NEXT(i);
1158 END LOOP;
1159 end if;
1160 exception
1161 when others then NULL;
1162 end lock_contact_source;
1163
1164 -- Start of comments
1165 --
1166 -- Procedure Name : validate_contact_source
1167 -- Description :
1168 -- Business Rules :
1169 -- Parameters :
1170 -- Version : 1.0
1171 -- End of comments
1172
1173 procedure validate_contact_source(p_api_version IN NUMBER,
1174 p_init_msg_list IN VARCHAR2 ,
1175 x_return_status OUT NOCOPY VARCHAR2,
1176 x_msg_count OUT NOCOPY NUMBER,
1177 x_msg_data OUT NOCOPY VARCHAR2,
1178 p_csov_rec IN csov_rec_type) is
1179 l_api_name CONSTANT VARCHAR2(30) := 'validate_contact_source';
1180 l_api_version CONSTANT NUMBER := 1;
1181 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1182 begin
1183 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
1184 G_PKG_NAME,
1185 p_init_msg_list,
1186 l_api_version,
1187 p_api_version,
1188 '_PUB',
1189 x_return_status);
1190 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1191 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1192 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1193 RAISE OKC_API.G_EXCEPTION_ERROR;
1194 END IF;
1195 --
1196 -- Call Before Logic Hook
1197 --
1198 g_csov_rec := p_csov_rec;
1199 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
1200 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1201 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1202 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1203 raise OKC_API.G_EXCEPTION_ERROR;
1204 END IF;
1205 reset(p_csov_rec);
1206 OKC_ROLE_SOURCE_PVT.validate_contact_source(p_api_version,
1207 p_init_msg_list,
1208 x_return_status,
1209 x_msg_count,
1210 x_msg_data,
1211 g_csov_rec);
1212 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1213 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1214 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1215 RAISE OKC_API.G_EXCEPTION_ERROR;
1216 END IF;
1217 --
1218 -- Call After Logic Hook
1219 --
1220 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
1221 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1222 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1223 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1224 raise OKC_API.G_EXCEPTION_ERROR;
1225 END IF;
1226 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1227 EXCEPTION
1228 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1229 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1230 (substr(l_api_name,1,26),
1231 G_PKG_NAME,
1232 'OKC_API.G_RET_STS_ERROR',
1233 x_msg_count,
1234 x_msg_data,
1235 '_PUB');
1236 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1237 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1238 (substr(l_api_name,1,26),
1239 G_PKG_NAME,
1240 'OKC_API.G_RET_STS_UNEXP_ERROR',
1241 x_msg_count,
1242 x_msg_data,
1243 '_PUB');
1244 WHEN OTHERS THEN
1245 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1246 (substr(l_api_name,1,26),
1247 G_PKG_NAME,
1248 'OTHERS',
1249 x_msg_count,
1250 x_msg_data,
1251 '_PUB');
1252 end validate_contact_source;
1253
1254 -- Start of comments
1255 --
1256 -- Procedure Name : validate_contact_source
1257 -- Description :
1258 -- Business Rules :
1259 -- Parameters :
1260 -- Version : 1.0
1261 -- End of comments
1262
1263 procedure validate_contact_source(p_api_version IN NUMBER,
1264 p_init_msg_list IN VARCHAR2 ,
1265 x_return_status OUT NOCOPY VARCHAR2,
1266 x_msg_count OUT NOCOPY NUMBER,
1267 x_msg_data OUT NOCOPY VARCHAR2,
1268 p_csov_tbl IN csov_tbl_type) is
1269 i NUMBER;
1270 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1271 begin
1272 OKC_API.init_msg_list(p_init_msg_list);
1273 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
1274 if (p_csov_tbl.COUNT>0) then
1275 i := p_csov_tbl.FIRST;
1276 LOOP
1277 validate_contact_source(p_api_version=>p_api_version,
1278 p_init_msg_list=>OKC_API.G_FALSE,
1279 x_return_status=>l_return_status,
1280 x_msg_count=>x_msg_count,
1281 x_msg_data=>x_msg_data,
1282 p_csov_rec=>p_csov_tbl(i));
1283 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1284 x_return_status := l_return_status;
1285 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1286 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
1287 x_return_status := l_return_status;
1288 end if;
1289 EXIT WHEN (i=p_csov_tbl.LAST);
1290 i := p_csov_tbl.NEXT(i);
1291 END LOOP;
1292 end if;
1293 exception
1294 when others then NULL;
1295 end validate_contact_source;
1296
1297 END OKC_ROLE_SOURCE_PUB;