[Home] [Help]
PACKAGE BODY: APPS.OKS_DEFAULTS_PUB
Source
1 PACKAGE BODY OKS_defaults_PUB AS
2 /* $Header: OKSPCDTB.pls 120.2 2006/03/20 17:53:45 skkoppul noship $ */
3
4 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
5 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKS_DEFAULTS_PUB';
6 G_CDTV_REC cdtv_Rec_Type;
7
8 G_MODULE CONSTANT VARCHAR2(250) := 'oks.plsql.'||g_pkg_name||'.';
9
10 FUNCTION do_Dates_Overlap (p_id NUMBER,
11 p_jtot_object_code VARCHAR2,
12 p_org_or_party_id VARCHAR2,
13 p_start_date DATE,
14 p_end_date DATE)
15 RETURN VARCHAR2 IS
16
17 CURSOR csr_duplicate_check IS
18 SELECT id, start_date, end_date
19 FROM oks_k_defaults
20 WHERE jtot_object_code = p_jtot_object_code
21 AND segment_id1 = p_org_or_party_id;
22
23 l_api_name CONSTANT VARCHAR2(30) := 'do_Dates_Overlap';
24
25 l_id NUMBER;
26 l_start_date DATE;
27 l_end_date DATE;
28 x_overlap_yn VARCHAR2(1) := 'N';
29 l_infinite_date DATE := TO_DATE(5373484,'j');
30
31 BEGIN
32
33 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
34 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
35 'Entered '||G_PKG_NAME ||'.'||l_api_name );
36 END IF;
37
38 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
39 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
40 'ID '||p_id ||' JTOT Obj Code '||p_jtot_object_code);
41 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
42 'Party/Org id ' || p_org_or_party_id ||
43 ' Start Date ' || to_char(p_start_date,'DD-MON-RRRR') ||
44 ' End Date ' || to_char(p_end_date,'DD-MON-RRRR'));
45 END IF;
46
47 FOR dup_check_rec IN csr_duplicate_check
48 LOOP
49 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
50 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
51 'ID '||dup_check_rec.id || ' Start Date ' || to_char(dup_check_rec.start_date,'DD-MON-RRRR') ||
52 ' End Date ' || to_char(dup_check_rec.end_date,'DD-MON-RRRR'));
53 END IF;
54 IF p_id IS NULL OR p_id <> dup_check_rec.id THEN
55
56 -- check if the dates overlap
57 IF ((dup_check_rec.start_date IS NULL AND dup_check_rec.end_date IS NULL) OR
58 (p_start_date IS NULL AND p_end_date IS NULL) OR
59 (dup_check_rec.end_date IS NULL AND p_end_date IS NULL) OR
60 (dup_check_rec.start_date IS NULL AND p_start_date IS NULL) OR
61 (dup_check_rec.start_date BETWEEN p_start_date AND NVL(p_end_date,l_infinite_date)) OR
62 (dup_check_rec.end_date BETWEEN p_start_date AND NVL(p_end_date,l_infinite_date)) OR
63 (p_start_date BETWEEN dup_check_rec.start_date AND NVL(dup_check_rec.end_date,l_infinite_date)) OR
64 (p_end_date BETWEEN dup_check_rec.start_date AND NVL(dup_check_rec.end_date,l_infinite_date)))
65 THEN
66 fnd_message.set_name('OKS','OKS_DATE_OVERLAP');
67 fnd_message.set_token('DATE','OKS_DATE_OVERLAP_GBSET',TRUE);
68 fnd_msg_pub.add;
69 x_overlap_yn := 'Y';
70 EXIT;
71 END IF; -- duplicate record found with dates overlap
72 END IF;
73 END LOOP;
74 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
75 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
76 'Leaving '||G_PKG_NAME ||'.'||l_api_name||' x_overlap_yn='||x_overlap_yn);
77 END IF;
78 RETURN x_overlap_yn;
79 EXCEPTION
80 WHEN OTHERS THEN
81 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
82 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,G_MODULE||l_api_name,
83 'Leaving '||G_PKG_NAME ||'.'||l_api_name||'.Others sqlcode = '
84 ||SQLCODE||', sqlerrm = '||SQLERRM);
85 END IF;
86 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
87 RETURN NULL;
88 END;
89
90 procedure reset(p_cdtv_rec IN cdtv_rec_type) is
91 begin
92 g_cdtv_rec.id := p_cdtv_rec.id;
93 g_cdtv_rec.object_version_number := p_cdtv_rec.object_version_number;
94 g_cdtv_rec.created_by := p_cdtv_rec.created_by;
95 g_cdtv_rec.creation_date := p_cdtv_rec.creation_date;
96 g_cdtv_rec.last_updated_by := p_cdtv_rec.last_updated_by;
97 g_cdtv_rec.last_update_date := p_cdtv_rec.last_update_date;
98 end reset;
99
100 -- Start of comments
101 --
102 -- Procedure Name : create_defaults
103 -- Description :
104 -- Business Rules :
105 -- Parameters :
106 -- Version : 1.0
107 -- End of comments
108
109 procedure Insert_defaults(p_api_version IN NUMBER,
110 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
111 x_return_status OUT NOCOPY VARCHAR2,
112 x_msg_count OUT NOCOPY NUMBER,
113 x_msg_data OUT NOCOPY VARCHAR2,
114 p_cdtv_rec IN cdtv_rec_type,
115 x_cdtv_rec OUT NOCOPY cdtv_rec_type) is
116
117 l_api_name CONSTANT VARCHAR2(30) := 'Insert_defaults';
118 l_api_version CONSTANT NUMBER := 1;
119 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
120 l_overlap_yn VARCHAR2(1);
121
122 begin
123 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
124 p_init_msg_list,
125 '_PUB',
126 x_return_status);
127
128 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
129 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
130 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
131 RAISE OKC_API.G_EXCEPTION_ERROR;
132 END IF;
133 --
134 -- Call Before Logic Hook
135 --
136 g_cdtv_rec := p_cdtv_rec;
137 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
138 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
139 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
140 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
141 raise OKC_API.G_EXCEPTION_ERROR;
142 END IF;
143 reset(p_cdtv_rec);
144
145 -- Check for operlapping dates
146 l_overlap_yn := do_Dates_Overlap (
147 p_id => NULL,
148 p_jtot_object_code => g_cdtv_rec.jtot_object_code,
149 p_org_or_party_id => g_cdtv_rec.segment_id1,
150 p_start_date => g_cdtv_rec.start_date,
151 p_end_date => g_cdtv_rec.end_date
152 );
153 IF l_overlap_yn IS NULL THEN
154 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
155 ELSIF l_overlap_yn = 'Y' THEN
156 RAISE OKC_API.G_EXCEPTION_ERROR;
157 END IF;
158
159 OKS_defaults_PVT.Insert_defaults(p_api_version,
160 p_init_msg_list,
161 x_return_status,
162 x_msg_count,
163 x_msg_data,
164 g_cdtv_rec,
165 x_cdtv_rec);
166 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
167 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
168 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
169 RAISE OKC_API.G_EXCEPTION_ERROR;
170 END IF;
171 --
172 -- Call After Logic Hook
173 --
174 g_cdtv_rec := x_cdtv_rec;
175 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
176 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
177 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
178 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
179 raise OKC_API.G_EXCEPTION_ERROR;
180 END IF;
181 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
182 EXCEPTION
183 WHEN OKC_API.G_EXCEPTION_ERROR THEN
184 x_return_status := OKC_API.HANDLE_EXCEPTIONS
185 (substr(l_api_name,1,26),
186 G_PKG_NAME,
187 'OKC_API.G_RET_STS_ERROR',
188 x_msg_count,
189 x_msg_data,
190 '_PUB');
191 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_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_UNEXP_ERROR',
196 x_msg_count,
197 x_msg_data,
198 '_PUB');
199 WHEN OTHERS THEN
200 x_return_status := OKC_API.HANDLE_EXCEPTIONS
201 (substr(l_api_name,1,26),
202 G_PKG_NAME,
203 'OTHERS',
204 x_msg_count,
205 x_msg_data,
206 '_PUB');
207 end Insert_defaults;
208
209 -- Start of comments
210 --
211 -- Procedure Name : create_default
212 -- Description :
213 -- Business Rules :
214 -- Parameters :
215 -- Version : 1.0
216 -- End of comments
217
218 procedure Insert_defaults(p_api_version IN NUMBER,
219 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
220 x_return_status OUT NOCOPY VARCHAR2,
221 x_msg_count OUT NOCOPY NUMBER,
222 x_msg_data OUT NOCOPY VARCHAR2,
223 p_cdtv_tbl IN cdtv_tbl_type,
224 x_cdtv_tbl OUT NOCOPY cdtv_tbl_type) is
225 i NUMBER;
226 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
227 begin
228 OKC_API.init_msg_list(p_init_msg_list);
229 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
230 if (p_cdtv_tbl.COUNT>0) then
231 i := p_cdtv_tbl.FIRST;
232 LOOP
233 Insert_defaults(p_api_version=>p_api_version,
234 p_init_msg_list=>OKC_API.G_FALSE,
235 x_return_status=>l_return_status,
236 x_msg_count=>x_msg_count,
237 x_msg_data=>x_msg_data,
238 p_cdtv_rec=>p_cdtv_tbl(i),
239 x_cdtv_rec=>x_cdtv_tbl(i));
240 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
241 x_return_status := l_return_status;
242 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
243 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
244 x_return_status := l_return_status;
245 end if;
246 EXIT WHEN (i=p_cdtv_tbl.LAST);
247 i := p_cdtv_tbl.NEXT(i);
248 END LOOP;
249 end if;
250 exception
251 when others then NULL;
252 end Insert_defaults;
253
254 procedure update_defaults(p_api_version IN NUMBER,
255 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
256 x_return_status OUT NOCOPY VARCHAR2,
257 x_msg_count OUT NOCOPY NUMBER,
258 x_msg_data OUT NOCOPY VARCHAR2,
259 p_cdtv_rec IN cdtv_rec_type,
260 x_cdtv_rec OUT NOCOPY cdtv_rec_type) is
261
262 l_api_name CONSTANT VARCHAR2(30) := 'update_defaults';
263 l_api_version CONSTANT NUMBER := 1;
264 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
265 l_overlap_yn VARCHAR2(1);
266
267 begin
268 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
269 p_init_msg_list,
270 '_PUB',
271 x_return_status);
272 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
273 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
274 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
275 RAISE OKC_API.G_EXCEPTION_ERROR;
276 END IF;
277 --
278 -- Call Before Logic Hook
279 --
280 g_cdtv_rec := p_cdtv_rec;
281 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
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 reset(p_cdtv_rec);
288
289 -- Check for overlapping dates
290 l_overlap_yn := do_Dates_Overlap (
291 p_id => g_cdtv_rec.id,
292 p_jtot_object_code => g_cdtv_rec.jtot_object_code,
293 p_org_or_party_id => g_cdtv_rec.segment_id1,
294 p_start_date => g_cdtv_rec.start_date,
295 p_end_date => g_cdtv_rec.end_date
296 );
297 IF l_overlap_yn IS NULL THEN
298 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
299 ELSIF l_overlap_yn = 'Y' THEN
300 RAISE OKC_API.G_EXCEPTION_ERROR;
301 END IF;
302
303 OKS_defaults_PVT.update_defaults(p_api_version,
304 p_init_msg_list,
305 x_return_status,
306 x_msg_count,
307 x_msg_data,
308 g_cdtv_rec,
309 x_cdtv_rec);
310 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
311 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
312 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
313 RAISE OKC_API.G_EXCEPTION_ERROR;
314 END IF;
315 --
316 -- Call After Logic Hook
317 --
318 g_cdtv_rec := x_cdtv_rec;
319 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
320 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
321 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
322 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
323 raise OKC_API.G_EXCEPTION_ERROR;
324 END IF;
325 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
326 EXCEPTION
327 WHEN OKC_API.G_EXCEPTION_ERROR THEN
328 x_return_status := OKC_API.HANDLE_EXCEPTIONS
329 (substr(l_api_name,1,26),
330 G_PKG_NAME,
331 'OKC_API.G_RET_STS_ERROR',
332 x_msg_count,
333 x_msg_data,
334 '_PUB');
335 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
336 x_return_status := OKC_API.HANDLE_EXCEPTIONS
337 (substr(l_api_name,1,26),
338 G_PKG_NAME,
339 'OKC_API.G_RET_STS_UNEXP_ERROR',
340 x_msg_count,
341 x_msg_data,
342 '_PUB');
343 WHEN OTHERS THEN
344 x_return_status := OKC_API.HANDLE_EXCEPTIONS
345 (substr(l_api_name,1,26),
346 G_PKG_NAME,
347 'OTHERS',
348 x_msg_count,
349 x_msg_data,
350 '_PUB');
351 end update_defaults;
352
353 -- Start of comments
354 --
355 -- Procedure Name : update_default
356 -- Description :
357 -- Business Rules :
358 -- Parameters :
359 -- Version : 1.0
360 -- End of comments
361
362 procedure update_defaults(p_api_version IN NUMBER,
363 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
364 x_return_status OUT NOCOPY VARCHAR2,
365 x_msg_count OUT NOCOPY NUMBER,
366 x_msg_data OUT NOCOPY VARCHAR2,
367 p_cdtv_tbl IN cdtv_tbl_type,
368 x_cdtv_tbl OUT NOCOPY cdtv_tbl_type) is
369 i NUMBER;
370 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
371 begin
372 OKC_API.init_msg_list(p_init_msg_list);
373 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
374 if (p_cdtv_tbl.COUNT>0) then
375 i := p_cdtv_tbl.FIRST;
376 LOOP
377 update_defaults(p_api_version=>p_api_version,
378 p_init_msg_list=>OKC_API.G_FALSE,
379 x_return_status=>l_return_status,
380 x_msg_count=>x_msg_count,
381 x_msg_data=>x_msg_data,
382 p_cdtv_rec=>p_cdtv_tbl(i),
383 x_cdtv_rec=>x_cdtv_tbl(i));
384 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
385 x_return_status := l_return_status;
386 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
387 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
388 x_return_status := l_return_status;
389 end if;
390 EXIT WHEN (i=p_cdtv_tbl.LAST);
391 i := p_cdtv_tbl.NEXT(i);
392 END LOOP;
393 end if;
394 exception
395 when others then NULL;
396 end update_defaults;
397
398 -- Start of comments
399 --
400 -- Procedure Name : delete_default
401 -- Description :
402 -- Business Rules :
403 -- Parameters :
404 -- Version : 1.0
405 -- End of comments
406
407 procedure delete_defaults(p_api_version IN NUMBER,
408 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
409 x_return_status OUT NOCOPY VARCHAR2,
410 x_msg_count OUT NOCOPY NUMBER,
411 x_msg_data OUT NOCOPY VARCHAR2,
412 p_cdtv_rec IN cdtv_rec_type) is
413 l_api_name CONSTANT VARCHAR2(30) := 'delete_defaults';
414 l_api_version CONSTANT NUMBER := 1;
415 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
416 begin
417 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
418 p_init_msg_list,
419 '_PUB',
420 x_return_status);
421 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
422 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
423 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
424 RAISE OKC_API.G_EXCEPTION_ERROR;
425 END IF;
426 --
427 -- Call Before Logic Hook
428 --
429 g_cdtv_rec := p_cdtv_rec;
430 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
431 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
432 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
433 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
434 raise OKC_API.G_EXCEPTION_ERROR;
435 END IF;
436 reset(p_cdtv_rec);
437 OKS_defaults_PVT.delete_defaults(p_api_version,
438 p_init_msg_list,
439 x_return_status,
440 x_msg_count,
441 x_msg_data,
442 g_cdtv_rec);
443 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
444 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
445 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
446 RAISE OKC_API.G_EXCEPTION_ERROR;
447 END IF;
448 --
449 -- Call After Logic Hook
450 --
451 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
452 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
453 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
454 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
455 raise OKC_API.G_EXCEPTION_ERROR;
456 END IF;
457 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
458 EXCEPTION
459 WHEN OKC_API.G_EXCEPTION_ERROR THEN
460 x_return_status := OKC_API.HANDLE_EXCEPTIONS
461 (substr(l_api_name,1,26),
462 G_PKG_NAME,
463 'OKC_API.G_RET_STS_ERROR',
464 x_msg_count,
465 x_msg_data,
466 '_PUB');
467 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
468 x_return_status := OKC_API.HANDLE_EXCEPTIONS
469 (substr(l_api_name,1,26),
470 G_PKG_NAME,
471 'OKC_API.G_RET_STS_UNEXP_ERROR',
472 x_msg_count,
473 x_msg_data,
474 '_PUB');
475 WHEN OTHERS THEN
476 x_return_status := OKC_API.HANDLE_EXCEPTIONS
477 (substr(l_api_name,1,26),
478 G_PKG_NAME,
479 'OTHERS',
480 x_msg_count,
481 x_msg_data,
482 '_PUB');
483 end delete_defaults;
484
485 -- Start of comments
486 --
487 -- Procedure Name : delete_defaults
488 -- Description :
489 -- Business Rules :
490 -- Parameters :
491 -- Version : 1.0
492 -- End of comments
493
494 procedure delete_defaults(p_api_version IN NUMBER,
495 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
496 x_return_status OUT NOCOPY VARCHAR2,
497 x_msg_count OUT NOCOPY NUMBER,
498 x_msg_data OUT NOCOPY VARCHAR2,
499 p_cdtv_tbl IN cdtv_tbl_type) is
500 i NUMBER;
501 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
502 begin
503 OKC_API.init_msg_list(p_init_msg_list);
504 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
505 if (p_cdtv_tbl.COUNT>0) then
506 i := p_cdtv_tbl.FIRST;
507 LOOP
508 delete_defaults(p_api_version=>p_api_version,
509 p_init_msg_list=>OKC_API.G_FALSE,
510 x_return_status=>l_return_status,
511 x_msg_count=>x_msg_count,
512 x_msg_data=>x_msg_data,
513 p_cdtv_rec=>p_cdtv_tbl(i));
514 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
515 x_return_status := l_return_status;
516 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
517 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
518 x_return_status := l_return_status;
519 end if;
520 EXIT WHEN (i=p_cdtv_tbl.LAST);
521 i := p_cdtv_tbl.NEXT(i);
522 END LOOP;
523 end if;
524 exception
525 when others then NULL;
526 end delete_defaults;
527
528 -- Start of comments
529 --
530 -- Procedure Name : lock_defaults
531 -- Description :
532 -- Business Rules :
533 -- Parameters :
534 -- Version : 1.0
535 -- End of comments
536
537 procedure lock_defaults(p_api_version IN NUMBER,
538 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
539 x_return_status OUT NOCOPY VARCHAR2,
540 x_msg_count OUT NOCOPY NUMBER,
541 x_msg_data OUT NOCOPY VARCHAR2,
542 p_cdtv_rec IN cdtv_rec_type) is
543 l_api_name CONSTANT VARCHAR2(30) := 'lock_defaults';
544 l_api_version CONSTANT NUMBER := 1;
545 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
546 begin
547 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
548 p_init_msg_list,
549 '_PUB',
550 x_return_status);
551 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
552 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
553 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
554 RAISE OKC_API.G_EXCEPTION_ERROR;
555 END IF;
556 --
557 -- Call Before Logic Hook
558 --
559 g_cdtv_rec := p_cdtv_rec;
560 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
561 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
562 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
563 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
564 raise OKC_API.G_EXCEPTION_ERROR;
565 END IF;
566 reset(p_cdtv_rec);
567 OKS_defaults_PVT.lock_defaults(p_api_version,
568 p_init_msg_list,
569 x_return_status,
570 x_msg_count,
571 x_msg_data,
572 g_cdtv_rec);
573 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
574 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
575 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
576 RAISE OKC_API.G_EXCEPTION_ERROR;
577 END IF;
578 --
579 -- Call After Logic Hook
580 --
581 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
582 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
583 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
584 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
585 raise OKC_API.G_EXCEPTION_ERROR;
586 END IF;
587 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
588 EXCEPTION
589 WHEN OKC_API.G_EXCEPTION_ERROR THEN
590 x_return_status := OKC_API.HANDLE_EXCEPTIONS
591 (substr(l_api_name,1,26),
592 G_PKG_NAME,
593 'OKC_API.G_RET_STS_ERROR',
594 x_msg_count,
595 x_msg_data,
596 '_PUB');
597 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
598 x_return_status := OKC_API.HANDLE_EXCEPTIONS
599 (substr(l_api_name,1,26),
600 G_PKG_NAME,
601 'OKC_API.G_RET_STS_UNEXP_ERROR',
602 x_msg_count,
603 x_msg_data,
604 '_PUB');
605 WHEN OTHERS THEN
606 x_return_status := OKC_API.HANDLE_EXCEPTIONS
607 (substr(l_api_name,1,26),
608 G_PKG_NAME,
609 'OTHERS',
610 x_msg_count,
611 x_msg_data,
612 '_PUB');
613 end lock_defaults;
614
615 -- Start of comments
616 --
617 -- Procedure Name : lock_defaults
618 -- Description :
619 -- Business Rules :
620 -- Parameters :
621 -- Version : 1.0
622 -- End of comments
623
624 procedure lock_defaults(p_api_version IN NUMBER,
625 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
626 x_return_status OUT NOCOPY VARCHAR2,
627 x_msg_count OUT NOCOPY NUMBER,
628 x_msg_data OUT NOCOPY VARCHAR2,
629 p_cdtv_tbl IN cdtv_tbl_type) is
630 i NUMBER;
631 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
632 begin
633 OKC_API.init_msg_list(p_init_msg_list);
634 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
635 if (p_cdtv_tbl.COUNT>0) then
636 i := p_cdtv_tbl.FIRST;
637 LOOP
638 lock_defaults(p_api_version=>p_api_version,
639 p_init_msg_list=>OKC_API.G_FALSE,
640 x_return_status=>l_return_status,
641 x_msg_count=>x_msg_count,
642 x_msg_data=>x_msg_data,
643 p_cdtv_rec=>p_cdtv_tbl(i));
644 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
645 x_return_status := l_return_status;
646 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
647 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
648 x_return_status := l_return_status;
649 end if;
650 EXIT WHEN (i=p_cdtv_tbl.LAST);
651 i := p_cdtv_tbl.NEXT(i);
652 END LOOP;
653 end if;
654 exception
655 when others then NULL;
656 end lock_defaults;
657
658 -- Start of comments
659 --
660 -- Procedure Name : validate_defaults
661 -- Description :
662 -- Business Rules :
663 -- Parameters :
664 -- Version : 1.0
665 -- End of comments
666
667 procedure validate_defaults(p_api_version IN NUMBER,
668 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
669 x_return_status OUT NOCOPY VARCHAR2,
670 x_msg_count OUT NOCOPY NUMBER,
671 x_msg_data OUT NOCOPY VARCHAR2,
672 p_cdtv_rec IN cdtv_rec_type) is
673 l_api_name CONSTANT VARCHAR2(30) := 'validate_defaults';
674 l_api_version CONSTANT NUMBER := 1;
675 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
676 begin
677 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
678 p_init_msg_list,
679 '_PUB',
680 x_return_status);
681 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
682 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
683 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
684 RAISE OKC_API.G_EXCEPTION_ERROR;
685 END IF;
686 --
687 -- Call Before Logic Hook
688 --
689 g_cdtv_rec := p_cdtv_rec;
690 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
691 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
692 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
693 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
694 raise OKC_API.G_EXCEPTION_ERROR;
695 END IF;
696 reset(p_cdtv_rec);
697 OKS_defaults_PVT.validate_defaults(p_api_version,
698 p_init_msg_list,
699 x_return_status,
700 x_msg_count,
701 x_msg_data,
702 g_cdtv_rec);
703 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
704 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
705 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
706 RAISE OKC_API.G_EXCEPTION_ERROR;
707 END IF;
708 --
709 -- Call After Logic Hook
710 --
711 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
712 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
713 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
714 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
715 raise OKC_API.G_EXCEPTION_ERROR;
716 END IF;
717 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
718
719 EXCEPTION
720 WHEN OKC_API.G_EXCEPTION_ERROR THEN
721 x_return_status := OKC_API.HANDLE_EXCEPTIONS
722 (substr(l_api_name,1,26),
723 G_PKG_NAME,
724 'OKC_API.G_RET_STS_ERROR',
725 x_msg_count,
726 x_msg_data,
727 '_PUB');
728 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
729 x_return_status := OKC_API.HANDLE_EXCEPTIONS
730 (substr(l_api_name,1,26),
731 G_PKG_NAME,
732 'OKC_API.G_RET_STS_UNEXP_ERROR',
733 x_msg_count,
734 x_msg_data,
735 '_PUB');
736 WHEN OTHERS THEN
737 x_return_status := OKC_API.HANDLE_EXCEPTIONS
738 (substr(l_api_name,1,26),
739 G_PKG_NAME,
740 'OTHERS',
741 x_msg_count,
742 x_msg_data,
743 '_PUB');
744 end validate_defaults;
745
746 -- Start of comments
747 --
748 -- Procedure Name : validate_defaults
749 -- Description :
750 -- Business Rules :
751 -- Parameters :
752 -- Version : 1.0
753 -- End of comments
754
755 procedure validate_defaults(p_api_version IN NUMBER,
756 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
757 x_return_status OUT NOCOPY VARCHAR2,
758 x_msg_count OUT NOCOPY NUMBER,
759 x_msg_data OUT NOCOPY VARCHAR2,
760 p_cdtv_tbl IN cdtv_tbl_type) is
761 i NUMBER;
762 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
763 begin
764 OKC_API.init_msg_list(p_init_msg_list);
765 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
766 if (p_cdtv_tbl.COUNT>0) then
767 i := p_cdtv_tbl.FIRST;
768 LOOP
769 validate_defaults(p_api_version=>p_api_version,
770 p_init_msg_list=>OKC_API.G_FALSE,
771 x_return_status=>l_return_status,
772 x_msg_count=>x_msg_count,
773 x_msg_data=>x_msg_data,
774 p_cdtv_rec=>p_cdtv_tbl(i));
775 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
776 x_return_status := l_return_status;
777 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
778 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
779 x_return_status := l_return_status;
780 end if;
781 EXIT WHEN (i=p_cdtv_tbl.LAST);
782 i := p_cdtv_tbl.NEXT(i);
783 END LOOP;
784 end if;
785 exception
786 when others then NULL;
787 end validate_defaults;
788 end OKS_defaults_PUB;