[Home] [Help]
PACKAGE BODY: APPS.OKS_TEMPLATE_SET_PUB
Source
1 PACKAGE BODY OKS_TEMPLATE_SET_PUB AS
2 /* $Header: OKSPTMPB.pls 120.5 2005/10/27 16:32:25 skekkar noship $ */
3
4
5 ------------------------------------------------------------------------------
6 -- GLOBAL CONSTANTS
7 ------------------------------------------------------------------------------
8
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 G_MODULE CONSTANT VARCHAR2(250) := 'oks.plsql.'||g_pkg_name||'.';
11 G_APPLICATION_ID CONSTANT NUMBER :=515; -- OKS Application
12
13 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
14 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
15
16 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
17 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
18 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
19
20 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKS_UNEXPECTED_ERROR';
21 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
22 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
23
24 G_ITEM_TYPE CONSTANT VARCHAR2(30) := 'OKSKPRCS';
25
26 ---------------------------------------------------------------------------
27 -- PROCEDURE qc
28 ---------------------------------------------------------------------------
29 PROCEDURE qc IS
30 BEGIN
31 OKS_TEMPLATE_SET_PVT.qc;
32 END qc;
33
34 ---------------------------------------------------------------------------
35 -- PROCEDURE change_version
36 ---------------------------------------------------------------------------
37 PROCEDURE change_version IS
38 BEGIN
39 OKS_TEMPLATE_SET_PVT.change_version;
40 END change_version;
41
42 ---------------------------------------------------------------------------
43 -- PROCEDURE api_copy
44 ---------------------------------------------------------------------------
45 PROCEDURE api_copy IS
46 BEGIN
47 OKS_TEMPLATE_SET_PVT.api_copy;
48 END api_copy;
49
50 ---------------------------------------------------------------------------
51 -- PROCEDURE validate_row
52 ---------------------------------------------------------------------------
53 -----------------------------------------
54 -- validate_row for:OKS_TEMPLATE_SET_V --
55 -----------------------------------------
56 PROCEDURE validate_row(
57 p_api_version IN NUMBER,
58 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
59 x_return_status OUT NOCOPY VARCHAR2,
60 x_msg_count OUT NOCOPY NUMBER,
61 x_msg_data OUT NOCOPY VARCHAR2,
62 p_tmsv_rec IN tmsv_rec_type) IS
63
64 l_api_version CONSTANT NUMBER := 1;
65 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
66 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
67 BEGIN
68 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
69 G_PKG_NAME,
70 p_init_msg_list,
71 l_api_version,
72 p_api_version,
73 '_PUB',
74 x_return_status);
75 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
76 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
77 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
78 RAISE OKC_API.G_EXCEPTION_ERROR;
79 END IF;
80
81 OKC_UTIL.call_user_hook
82 (
83 x_return_status => x_return_status,
84 p_package_name => g_pkg_name,
85 p_procedure_name => l_api_name,
86 p_before_after => 'B'
87 );
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 OKS_TEMPLATE_SET_PVT.validate_row(p_api_version => l_api_version,
94 p_init_msg_list => p_init_msg_list,
95 x_return_status => x_return_status,
96 x_msg_count => x_msg_count,
97 x_msg_data => x_msg_data,
98 p_tmsv_rec => p_tmsv_rec );
99
100 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
101 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
102 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
103 raise OKC_API.G_EXCEPTION_ERROR;
104 End If;
105
106 OKC_UTIL.call_user_hook
107 (
108 x_return_status => x_return_status,
109 p_package_name => g_pkg_name,
110 p_procedure_name => l_api_name,
111 p_before_after => 'A'
112 );
113
114 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
115 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
116 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
117 raise OKC_API.G_EXCEPTION_ERROR;
118 End If;
119
120 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
121 x_return_status := l_return_status;
122
123 EXCEPTION
124 WHEN OKC_API.G_EXCEPTION_ERROR THEN
125 x_return_status := OKC_API.HANDLE_EXCEPTIONS
126 (
127 l_api_name,
128 G_PKG_NAME,
129 'OKC_API.G_RET_STS_ERROR',
130 x_msg_count,
131 x_msg_data,
132 '_PUB'
133 );
134 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
135 x_return_status := OKC_API.HANDLE_EXCEPTIONS
136 (
137 l_api_name,
138 G_PKG_NAME,
139 'OKC_API.G_RET_STS_UNEXP_ERROR',
140 x_msg_count,
141 x_msg_data,
142 '_PUB'
143 );
144 WHEN OTHERS THEN
145 x_return_status := OKC_API.HANDLE_EXCEPTIONS
146 (
147 l_api_name,
148 G_PKG_NAME,
149 'OTHERS',
150 x_msg_count,
151 x_msg_data,
152 '_PUB'
153 );
154 END validate_row;
155 ----------------------------------------------------
156 -- PL/SQL TBL validate_row for:OKS_TEMPLATE_SET_V --
157 ----------------------------------------------------
158 PROCEDURE validate_row(
159 p_api_version IN NUMBER,
160 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
161 x_return_status OUT NOCOPY VARCHAR2,
162 x_msg_count OUT NOCOPY NUMBER,
163 x_msg_data OUT NOCOPY VARCHAR2,
164 p_tmsv_tbl IN tmsv_tbl_type,
165 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
166
167 l_api_version CONSTANT NUMBER := 1;
168 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
169 l_return_status VARCHAR2(1);
170 BEGIN
171 OKC_API.init_msg_list(p_init_msg_list);
172 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
173 G_PKG_NAME,
174 p_init_msg_list,
175 l_api_version,
176 p_api_version,
177 '_PUB',
178 x_return_status);
179 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
180 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
181 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
182 RAISE OKC_API.G_EXCEPTION_ERROR;
183 END IF;
184
185 OKC_UTIL.call_user_hook
186 (
187 x_return_status => x_return_status,
188 p_package_name => g_pkg_name,
189 p_procedure_name => l_api_name,
190 p_before_after => 'B'
191 );
192 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
193 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
194 ElsIf (x_return_status = OKC_API.G_RET_STS_ERROR) Then
195 raise OKC_API.G_EXCEPTION_ERROR;
196 End If;
197 OKS_TEMPLATE_SET_PVT.validate_row(p_api_version => l_api_version,
198 p_init_msg_list => p_init_msg_list,
199 x_return_status => x_return_status,
200 x_msg_count => x_msg_count,
201 x_msg_data => x_msg_data,
202 p_tmsv_tbl => p_tmsv_tbl,
203 px_error_tbl => px_error_tbl);
204
205 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
206 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
207 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
208 raise OKC_API.G_EXCEPTION_ERROR;
209 End If;
210
211 OKC_UTIL.call_user_hook
212 (
213 x_return_status => x_return_status,
214 p_package_name => g_pkg_name,
215 p_procedure_name => l_api_name,
216 p_before_after => 'A'
217 );
218
219 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
220 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
221 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
222 raise OKC_API.G_EXCEPTION_ERROR;
223 End If;
224 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
225 x_return_status := l_return_status;
226 EXCEPTION
227 WHEN OKC_API.G_EXCEPTION_ERROR THEN
228 x_return_status := OKC_API.HANDLE_EXCEPTIONS
229 (
230 l_api_name,
231 G_PKG_NAME,
232 'OKC_API.G_RET_STS_ERROR',
233 x_msg_count,
234 x_msg_data,
235 '_PUB'
236 );
237 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
238 x_return_status := OKC_API.HANDLE_EXCEPTIONS
239 (
240 l_api_name,
241 G_PKG_NAME,
242 'OKC_API.G_RET_STS_UNEXP_ERROR',
243 x_msg_count,
244 x_msg_data,
245 '_PUB'
246 );
247 WHEN OTHERS THEN
248 x_return_status := OKC_API.HANDLE_EXCEPTIONS
249 (
250 l_api_name,
251 G_PKG_NAME,
252 'OTHERS',
253 x_msg_count,
254 x_msg_data,
255 '_PUB'
256 );
257 END validate_row;
258
259 ----------------------------------------------------
260 -- PL/SQL TBL validate_row for:OKS_TEMPLATE_SET_V --
261 ----------------------------------------------------
262 PROCEDURE validate_row(
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_tmsv_tbl IN tmsv_tbl_type) IS
269
270 l_api_version CONSTANT NUMBER := 1;
271 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
272 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
273 BEGIN
274 OKC_API.init_msg_list(p_init_msg_list);
275 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
276 G_PKG_NAME,
277 p_init_msg_list,
278 l_api_version,
279 p_api_version,
280 '_PUB',
281 x_return_status);
282 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
283 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
284 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
285 RAISE OKC_API.G_EXCEPTION_ERROR;
286 END IF;
287
288 OKC_UTIL.call_user_hook
289 (
290 x_return_status => x_return_status,
291 p_package_name => g_pkg_name,
292 p_procedure_name => l_api_name,
293 p_before_after => 'B'
294 );
295 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
296 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
297 ElsIf (x_return_status = OKC_API.G_RET_STS_ERROR) Then
298 raise OKC_API.G_EXCEPTION_ERROR;
299 End If;
300 OKS_TEMPLATE_SET_PVT.validate_row(p_api_version => l_api_version,
301 p_init_msg_list => p_init_msg_list,
302 x_return_status => x_return_status,
303 x_msg_count => x_msg_count,
304 x_msg_data => x_msg_data,
305 p_tmsv_tbl => p_tmsv_tbl);
306
307 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
308 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
309 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
310 raise OKC_API.G_EXCEPTION_ERROR;
311 End If;
312
313 OKC_UTIL.call_user_hook
314 (
315 x_return_status => x_return_status,
316 p_package_name => g_pkg_name,
317 p_procedure_name => l_api_name,
318 p_before_after => 'A'
319 );
320
321 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
322 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
323 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
324 raise OKC_API.G_EXCEPTION_ERROR;
325 End If;
326 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
327 x_return_status := l_return_status;
328 EXCEPTION
329 WHEN OKC_API.G_EXCEPTION_ERROR THEN
330 x_return_status := OKC_API.HANDLE_EXCEPTIONS
331 (
332 l_api_name,
333 G_PKG_NAME,
334 'OKC_API.G_RET_STS_ERROR',
335 x_msg_count,
336 x_msg_data,
337 '_PUB'
338 );
339 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
340 x_return_status := OKC_API.HANDLE_EXCEPTIONS
341 (
342 l_api_name,
343 G_PKG_NAME,
344 'OKC_API.G_RET_STS_UNEXP_ERROR',
345 x_msg_count,
346 x_msg_data,
347 '_PUB'
348 );
349 WHEN OTHERS THEN
350 x_return_status := OKC_API.HANDLE_EXCEPTIONS
351 (
352 l_api_name,
353 G_PKG_NAME,
354 'OTHERS',
355 x_msg_count,
356 x_msg_data,
357 '_PUB'
358 );
359 END validate_row;
360
361 ---------------------------------------------------------------------------
362 -- PROCEDURE insert_row
363 ---------------------------------------------------------------------------
364 ----------------------------------------
365 -- insert_row for :OKS_TEMPLATE_SET_V --
366 ----------------------------------------
367 PROCEDURE insert_row(
368 p_api_version IN NUMBER,
369 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
370 x_return_status OUT NOCOPY VARCHAR2,
371 x_msg_count OUT NOCOPY NUMBER,
372 x_msg_data OUT NOCOPY VARCHAR2,
373 p_tmsv_rec IN tmsv_rec_type,
374 x_tmsv_rec OUT NOCOPY tmsv_rec_type) IS
375
376 l_api_version CONSTANT NUMBER := 1;
377 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
378 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
379 BEGIN
380 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
381 G_PKG_NAME,
382 p_init_msg_list,
383 l_api_version,
384 p_api_version,
385 '_PUB',
386 x_return_status);
387 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
388 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
389 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
390 RAISE OKC_API.G_EXCEPTION_ERROR;
391 END IF;
392
393 OKC_UTIL.call_user_hook
394 (
395 x_return_status => x_return_status,
396 p_package_name => g_pkg_name,
397 p_procedure_name => l_api_name,
398 p_before_after => 'B'
399 );
400 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
401 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
402 ElsIf (x_return_status = OKC_API.G_RET_STS_ERROR) Then
403 raise OKC_API.G_EXCEPTION_ERROR;
404 End If;
405 OKS_TEMPLATE_SET_PVT.insert_row(p_api_version => l_api_version,
406 p_init_msg_list => p_init_msg_list,
407 x_return_status => x_return_status,
408 x_msg_count => x_msg_count,
409 x_msg_data => x_msg_data,
410 p_tmsv_rec => p_tmsv_rec,
411 x_tmsv_rec => x_tmsv_rec);
412
413
414 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
415 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
416 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
417 raise OKC_API.G_EXCEPTION_ERROR;
418 End If;
419
420 OKC_UTIL.call_user_hook
421 (
422 x_return_status => x_return_status,
423 p_package_name => g_pkg_name,
424 p_procedure_name => l_api_name,
425 p_before_after => 'A'
426 );
427
428 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
429 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
430 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
431 raise OKC_API.G_EXCEPTION_ERROR;
432 End If;
433
434 x_return_status := l_return_status;
435 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
436 EXCEPTION
437 WHEN OKC_API.G_EXCEPTION_ERROR THEN
438 x_return_status := OKC_API.HANDLE_EXCEPTIONS
439 (
440 l_api_name,
441 G_PKG_NAME,
442 'OKC_API.G_RET_STS_ERROR',
443 x_msg_count,
444 x_msg_data,
445 '_PUB'
446 );
447 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
448 x_return_status := OKC_API.HANDLE_EXCEPTIONS
449 (
450 l_api_name,
451 G_PKG_NAME,
452 'OKC_API.G_RET_STS_UNEXP_ERROR',
453 x_msg_count,
454 x_msg_data,
455 '_PUB'
456 );
457 WHEN OTHERS THEN
458 x_return_status := OKC_API.HANDLE_EXCEPTIONS
459 (
460 l_api_name,
461 G_PKG_NAME,
462 'OTHERS',
463 x_msg_count,
464 x_msg_data,
465 '_PUB'
466 );
467 END insert_row;
468 ----------------------------------------
469 -- PL/SQL TBL insert_row for:TMSV_TBL --
470 ----------------------------------------
471 PROCEDURE insert_row(
472 p_api_version IN NUMBER,
473 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
474 x_return_status OUT NOCOPY VARCHAR2,
475 x_msg_count OUT NOCOPY NUMBER,
476 x_msg_data OUT NOCOPY VARCHAR2,
477 p_tmsv_tbl IN tmsv_tbl_type,
478 x_tmsv_tbl OUT NOCOPY tmsv_tbl_type,
479 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
480
481 l_api_version CONSTANT NUMBER := 1;
482 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
483 l_return_status VARCHAR2(1);
484 BEGIN
485 OKC_API.init_msg_list(p_init_msg_list);
486 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
487 G_PKG_NAME,
488 p_init_msg_list,
489 l_api_version,
490 p_api_version,
491 '_PUB',
492 x_return_status);
493 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
494 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
495 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
496 RAISE OKC_API.G_EXCEPTION_ERROR;
497 END IF;
498
499 OKC_UTIL.call_user_hook
500 (
501 x_return_status => x_return_status,
502 p_package_name => g_pkg_name,
503 p_procedure_name => l_api_name,
504 p_before_after => 'B'
505 );
506 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
507 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
508 ElsIf (x_return_status = OKC_API.G_RET_STS_ERROR) Then
509 raise OKC_API.G_EXCEPTION_ERROR;
510 End If;
511 OKS_TEMPLATE_SET_PVT.insert_row(p_api_version => l_api_version,
512 p_init_msg_list => p_init_msg_list,
513 x_return_status => x_return_status,
514 x_msg_count => x_msg_count,
515 x_msg_data => x_msg_data,
516 p_tmsv_tbl => p_tmsv_tbl,
517 x_tmsv_tbl => x_tmsv_tbl,
518 px_error_tbl => px_error_tbl);
519
520 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
521 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
522 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
523 raise OKC_API.G_EXCEPTION_ERROR;
524 End If;
525
526 OKC_UTIL.call_user_hook
527 (
528 x_return_status => x_return_status,
529 p_package_name => g_pkg_name,
530 p_procedure_name => l_api_name,
531 p_before_after => 'A'
532 );
533
534 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
535 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
536 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
537 raise OKC_API.G_EXCEPTION_ERROR;
538 End If;
539
540 x_return_status := l_return_status;
541 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
542 EXCEPTION
543 WHEN OKC_API.G_EXCEPTION_ERROR THEN
544 x_return_status := OKC_API.HANDLE_EXCEPTIONS
545 (
546 l_api_name,
547 G_PKG_NAME,
548 'OKC_API.G_RET_STS_ERROR',
549 x_msg_count,
550 x_msg_data,
551 '_PUB'
552 );
553 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
554 x_return_status := OKC_API.HANDLE_EXCEPTIONS
555 (
556 l_api_name,
557 G_PKG_NAME,
558 'OKC_API.G_RET_STS_UNEXP_ERROR',
559 x_msg_count,
560 x_msg_data,
561 '_PUB'
562 );
563 WHEN OTHERS THEN
564 x_return_status := OKC_API.HANDLE_EXCEPTIONS
565 (
566 l_api_name,
567 G_PKG_NAME,
568 'OTHERS',
569 x_msg_count,
570 x_msg_data,
571 '_PUB'
572 );
573 END insert_row;
574
575 ----------------------------------------
576 -- PL/SQL TBL insert_row for:TMSV_TBL --
577 ----------------------------------------
578 PROCEDURE insert_row(
579 p_api_version IN NUMBER,
580 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_msg_count OUT NOCOPY NUMBER,
583 x_msg_data OUT NOCOPY VARCHAR2,
584 p_tmsv_tbl IN tmsv_tbl_type,
585 x_tmsv_tbl OUT NOCOPY tmsv_tbl_type) IS
586
587 l_api_version CONSTANT NUMBER := 1;
588 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
589 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
590 BEGIN
591 OKC_API.init_msg_list(p_init_msg_list);
592 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
593 G_PKG_NAME,
594 p_init_msg_list,
595 l_api_version,
596 p_api_version,
597 '_PUB',
598 x_return_status);
599 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
600 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
601 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
602 RAISE OKC_API.G_EXCEPTION_ERROR;
603 END IF;
604
605 OKC_UTIL.call_user_hook
606 (
607 x_return_status => x_return_status,
608 p_package_name => g_pkg_name,
609 p_procedure_name => l_api_name,
610 p_before_after => 'B'
611 );
612 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
613 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
614 ElsIf (x_return_status = OKC_API.G_RET_STS_ERROR) Then
615 raise OKC_API.G_EXCEPTION_ERROR;
616 End If;
617 OKS_TEMPLATE_SET_PVT.insert_row(p_api_version => l_api_version,
618 p_init_msg_list => p_init_msg_list,
619 x_return_status => x_return_status,
620 x_msg_count => x_msg_count,
621 x_msg_data => x_msg_data,
622 p_tmsv_tbl => p_tmsv_tbl,
623 x_tmsv_tbl => x_tmsv_tbl);
624
625
626 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
627 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
628 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
629 raise OKC_API.G_EXCEPTION_ERROR;
630 End If;
631
632 OKC_UTIL.call_user_hook
633 (
634 x_return_status => x_return_status,
635 p_package_name => g_pkg_name,
636 p_procedure_name => l_api_name,
637 p_before_after => 'A'
638 );
639
640 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
641 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
642 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
643 raise OKC_API.G_EXCEPTION_ERROR;
644 End If;
645
646 x_return_status := l_return_status;
647 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
648 EXCEPTION
649 WHEN OKC_API.G_EXCEPTION_ERROR THEN
650 x_return_status := OKC_API.HANDLE_EXCEPTIONS
651 (
652 l_api_name,
653 G_PKG_NAME,
654 'OKC_API.G_RET_STS_ERROR',
655 x_msg_count,
656 x_msg_data,
657 '_PUB'
658 );
659 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
660 x_return_status := OKC_API.HANDLE_EXCEPTIONS
661 (
662 l_api_name,
663 G_PKG_NAME,
664 'OKC_API.G_RET_STS_UNEXP_ERROR',
665 x_msg_count,
666 x_msg_data,
667 '_PUB'
668 );
669 WHEN OTHERS THEN
670 x_return_status := OKC_API.HANDLE_EXCEPTIONS
671 (
672 l_api_name,
673 G_PKG_NAME,
674 'OTHERS',
675 x_msg_count,
676 x_msg_data,
677 '_PUB'
678 );
679 END insert_row;
680
681 ---------------------------------------------------------------------------
682 -- PROCEDURE lock_row
683 ---------------------------------------------------------------------------
684 --------------------------------------
685 -- lock_row for: OKS_TEMPLATE_SET_V --
686 --------------------------------------
687 PROCEDURE lock_row(
688 p_api_version IN NUMBER,
689 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
690 x_return_status OUT NOCOPY VARCHAR2,
691 x_msg_count OUT NOCOPY NUMBER,
692 x_msg_data OUT NOCOPY VARCHAR2,
693 p_tmsv_rec IN tmsv_rec_type) IS
694
695 l_api_version CONSTANT NUMBER := 1;
696 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
697 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
698 BEGIN
699 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
700 G_PKG_NAME,
701 p_init_msg_list,
702 l_api_version,
703 p_api_version,
704 '_PUB',
705 x_return_status);
706 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
707 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
708 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
709 RAISE OKC_API.G_EXCEPTION_ERROR;
710 END IF;
711
712 OKC_UTIL.call_user_hook
713 (
714 x_return_status => x_return_status,
715 p_package_name => g_pkg_name,
716 p_procedure_name => l_api_name,
717 p_before_after => 'B'
718 );
719 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
720 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
721 ElsIf (x_return_status = OKC_API.G_RET_STS_ERROR) Then
722 raise OKC_API.G_EXCEPTION_ERROR;
723 End If;
724 OKS_TEMPLATE_SET_PVT.lock_row(p_api_version => l_api_version,
725 p_init_msg_list => p_init_msg_list,
726 x_return_status => x_return_status,
727 x_msg_count => x_msg_count,
728 x_msg_data => x_msg_data,
729 p_tmsv_rec => p_tmsv_rec);
730
731
732 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
733 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
734 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
735 raise OKC_API.G_EXCEPTION_ERROR;
736 End If;
737
738 OKC_UTIL.call_user_hook
739 (
740 x_return_status => x_return_status,
741 p_package_name => g_pkg_name,
742 p_procedure_name => l_api_name,
743 p_before_after => 'A'
744 );
745
746 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
747 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
748 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
749 raise OKC_API.G_EXCEPTION_ERROR;
750 End If;
751 x_return_status := l_return_status;
752 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
753 EXCEPTION
754 WHEN OKC_API.G_EXCEPTION_ERROR THEN
755 x_return_status := OKC_API.HANDLE_EXCEPTIONS
756 (
757 l_api_name,
758 G_PKG_NAME,
759 'OKC_API.G_RET_STS_ERROR',
760 x_msg_count,
761 x_msg_data,
762 '_PUB'
763 );
764 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
765 x_return_status := OKC_API.HANDLE_EXCEPTIONS
766 (
767 l_api_name,
768 G_PKG_NAME,
769 'OKC_API.G_RET_STS_UNEXP_ERROR',
770 x_msg_count,
771 x_msg_data,
772 '_PUB'
773 );
774 WHEN OTHERS THEN
775 x_return_status := OKC_API.HANDLE_EXCEPTIONS
776 (
777 l_api_name,
778 G_PKG_NAME,
779 'OTHERS',
780 x_msg_count,
781 x_msg_data,
782 '_PUB'
783 );
784 END lock_row;
785 --------------------------------------
786 -- PL/SQL TBL lock_row for:TMSV_TBL --
787 --------------------------------------
788 PROCEDURE lock_row(
789 p_api_version IN NUMBER,
790 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
791 x_return_status OUT NOCOPY VARCHAR2,
792 x_msg_count OUT NOCOPY NUMBER,
793 x_msg_data OUT NOCOPY VARCHAR2,
794 p_tmsv_tbl IN tmsv_tbl_type,
795 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
796
797 l_api_version CONSTANT NUMBER := 1;
798 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
799 l_return_status VARCHAR2(1);
800 BEGIN
801 OKC_API.init_msg_list(p_init_msg_list);
802 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
803 G_PKG_NAME,
804 p_init_msg_list,
805 l_api_version,
806 p_api_version,
807 '_PUB',
808 x_return_status);
809 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
810 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
811 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
812 RAISE OKC_API.G_EXCEPTION_ERROR;
813 END IF;
814
815 OKC_UTIL.call_user_hook
816 (
817 x_return_status => x_return_status,
818 p_package_name => g_pkg_name,
819 p_procedure_name => l_api_name,
820 p_before_after => 'B'
821 );
822 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
823 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
824 ElsIf (x_return_status = OKC_API.G_RET_STS_ERROR) Then
825 raise OKC_API.G_EXCEPTION_ERROR;
826 End If;
827 OKS_TEMPLATE_SET_PVT.lock_row(p_api_version => l_api_version,
828 p_init_msg_list => p_init_msg_list,
829 x_return_status => x_return_status,
830 x_msg_count => x_msg_count,
831 x_msg_data => x_msg_data,
832 p_tmsv_tbl => p_tmsv_tbl,
833 px_error_tbl => px_error_tbl);
834
835
836 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
837 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
838 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
839 raise OKC_API.G_EXCEPTION_ERROR;
840 End If;
841
842 OKC_UTIL.call_user_hook
843 (
844 x_return_status => x_return_status,
845 p_package_name => g_pkg_name,
846 p_procedure_name => l_api_name,
847 p_before_after => 'A'
848 );
849
850 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
851 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
852 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
853 raise OKC_API.G_EXCEPTION_ERROR;
854 End If;
855
856 x_return_status := l_return_status;
857 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
858 EXCEPTION
859 WHEN OKC_API.G_EXCEPTION_ERROR THEN
860 x_return_status := OKC_API.HANDLE_EXCEPTIONS
861 (
862 l_api_name,
863 G_PKG_NAME,
864 'OKC_API.G_RET_STS_ERROR',
865 x_msg_count,
866 x_msg_data,
867 '_PUB'
868 );
869 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
870 x_return_status := OKC_API.HANDLE_EXCEPTIONS
871 (
872 l_api_name,
873 G_PKG_NAME,
874 'OKC_API.G_RET_STS_UNEXP_ERROR',
875 x_msg_count,
876 x_msg_data,
877 '_PUB'
878 );
879 WHEN OTHERS THEN
880 x_return_status := OKC_API.HANDLE_EXCEPTIONS
881 (
882 l_api_name,
883 G_PKG_NAME,
884 'OTHERS',
885 x_msg_count,
886 x_msg_data,
887 '_PUB'
888 );
889 END lock_row;
890 --------------------------------------
891 -- PL/SQL TBL lock_row for:TMSV_TBL --
892 --------------------------------------
893 PROCEDURE lock_row(
894 p_api_version IN NUMBER,
895 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
896 x_return_status OUT NOCOPY VARCHAR2,
897 x_msg_count OUT NOCOPY NUMBER,
898 x_msg_data OUT NOCOPY VARCHAR2,
899 p_tmsv_tbl IN tmsv_tbl_type) IS
900
901 l_api_version CONSTANT NUMBER := 1;
902 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
903 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
904 BEGIN
905 OKC_API.init_msg_list(p_init_msg_list);
906 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
907 G_PKG_NAME,
908 p_init_msg_list,
909 l_api_version,
910 p_api_version,
911 '_PUB',
912 x_return_status);
913 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
914 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
915 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
916 RAISE OKC_API.G_EXCEPTION_ERROR;
917 END IF;
918
919 OKC_UTIL.call_user_hook
920 (
921 x_return_status => x_return_status,
922 p_package_name => g_pkg_name,
923 p_procedure_name => l_api_name,
924 p_before_after => 'B'
925 );
926 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
927 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
928 ElsIf (x_return_status = OKC_API.G_RET_STS_ERROR) Then
929 raise OKC_API.G_EXCEPTION_ERROR;
930 End If;
931 OKS_TEMPLATE_SET_PVT.lock_row(p_api_version => l_api_version,
932 p_init_msg_list => p_init_msg_list,
933 x_return_status => x_return_status,
934 x_msg_count => x_msg_count,
935 x_msg_data => x_msg_data,
936 p_tmsv_tbl => p_tmsv_tbl);
937
938 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
939 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
940 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
941 raise OKC_API.G_EXCEPTION_ERROR;
942 End If;
943
944 OKC_UTIL.call_user_hook
945 (
946 x_return_status => x_return_status,
947 p_package_name => g_pkg_name,
948 p_procedure_name => l_api_name,
949 p_before_after => 'A'
950 );
951
952 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
953 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
954 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
955 raise OKC_API.G_EXCEPTION_ERROR;
956 End If;
957
958 x_return_status := l_return_status;
959 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
960 EXCEPTION
961 WHEN OKC_API.G_EXCEPTION_ERROR THEN
962 x_return_status := OKC_API.HANDLE_EXCEPTIONS
963 (
964 l_api_name,
965 G_PKG_NAME,
966 'OKC_API.G_RET_STS_ERROR',
967 x_msg_count,
968 x_msg_data,
969 '_PUB'
970 );
971 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
972 x_return_status := OKC_API.HANDLE_EXCEPTIONS
973 (
974 l_api_name,
975 G_PKG_NAME,
976 'OKC_API.G_RET_STS_UNEXP_ERROR',
977 x_msg_count,
978 x_msg_data,
979 '_PUB'
980 );
981 WHEN OTHERS THEN
982 x_return_status := OKC_API.HANDLE_EXCEPTIONS
983 (
984 l_api_name,
985 G_PKG_NAME,
986 'OTHERS',
987 x_msg_count,
988 x_msg_data,
989 '_PUB'
990 );
991 END lock_row;
992 ---------------------------------------------------------------------------
993 -- PROCEDURE update_row
994 ---------------------------------------------------------------------------
995 ---------------------------------------
996 -- update_row for:OKS_TEMPLATE_SET_V --
997 ---------------------------------------
998 PROCEDURE update_row(
999 p_api_version IN NUMBER,
1000 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1001 x_return_status OUT NOCOPY VARCHAR2,
1002 x_msg_count OUT NOCOPY NUMBER,
1003 x_msg_data OUT NOCOPY VARCHAR2,
1004 p_tmsv_rec IN tmsv_rec_type,
1005 x_tmsv_rec OUT NOCOPY tmsv_rec_type) IS
1006
1007 l_api_version CONSTANT NUMBER := 1;
1008 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1009 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1010 Begin
1011 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1012 G_PKG_NAME,
1013 p_init_msg_list,
1014 l_api_version,
1015 p_api_version,
1016 '_PUB',
1017 x_return_status);
1018 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1019 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1020 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1021 RAISE OKC_API.G_EXCEPTION_ERROR;
1022 END IF;
1023
1024 OKC_UTIL.call_user_hook
1025 (
1026 x_return_status => x_return_status,
1027 p_package_name => g_pkg_name,
1028 p_procedure_name => l_api_name,
1029 p_before_after => 'B'
1030 );
1031 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
1032 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1033 ElsIf (x_return_status = OKC_API.G_RET_STS_ERROR) Then
1034 raise OKC_API.G_EXCEPTION_ERROR;
1035 End If;
1036 OKS_TEMPLATE_SET_PVT.update_row(p_api_version => l_api_version,
1037 p_init_msg_list => p_init_msg_list,
1038 x_return_status => x_return_status,
1039 x_msg_count => x_msg_count,
1040 x_msg_data => x_msg_data,
1041 p_tmsv_rec => p_tmsv_rec,
1042 x_tmsv_rec => x_tmsv_rec);
1043
1044
1045
1046 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1047 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1048 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1049 raise OKC_API.G_EXCEPTION_ERROR;
1050 End If;
1051
1052 OKC_UTIL.call_user_hook
1053 (
1054 x_return_status => x_return_status,
1055 p_package_name => g_pkg_name,
1056 p_procedure_name => l_api_name,
1057 p_before_after => 'A'
1058 );
1059
1060 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1061 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1062 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1063 raise OKC_API.G_EXCEPTION_ERROR;
1064 End If;
1065
1066 x_return_status := l_return_status;
1067 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1068 EXCEPTION
1069 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1070 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1071 (
1072 l_api_name,
1073 G_PKG_NAME,
1074 'OKC_API.G_RET_STS_ERROR',
1075 x_msg_count,
1076 x_msg_data,
1077 '_PUB'
1078 );
1079 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1080 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1081 (
1082 l_api_name,
1083 G_PKG_NAME,
1084 'OKC_API.G_RET_STS_UNEXP_ERROR',
1085 x_msg_count,
1086 x_msg_data,
1087 '_PUB'
1088 );
1089 WHEN OTHERS THEN
1090 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1091 (
1092 l_api_name,
1093 G_PKG_NAME,
1094 'OTHERS',
1095 x_msg_count,
1096 x_msg_data,
1097 '_PUB'
1098 );
1099 END update_row;
1100 ----------------------------------------
1101 -- PL/SQL TBL update_row for:tmsv_tbl --
1102 ----------------------------------------
1103 PROCEDURE update_row(
1104 p_api_version IN NUMBER,
1105 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1106 x_return_status OUT NOCOPY VARCHAR2,
1107 x_msg_count OUT NOCOPY NUMBER,
1108 x_msg_data OUT NOCOPY VARCHAR2,
1109 p_tmsv_tbl IN tmsv_tbl_type,
1110 x_tmsv_tbl OUT NOCOPY tmsv_tbl_type,
1111 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1112
1113 l_api_version CONSTANT NUMBER := 1;
1114 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
1115 l_return_status VARCHAR2(1);
1116 BEGIN
1117 OKC_API.init_msg_list(p_init_msg_list);
1118 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1119 G_PKG_NAME,
1120 p_init_msg_list,
1121 l_api_version,
1122 p_api_version,
1123 '_PUB',
1124 x_return_status);
1125 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1126 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1127 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1128 RAISE OKC_API.G_EXCEPTION_ERROR;
1129 END IF;
1130
1131 OKC_UTIL.call_user_hook
1132 (
1133 x_return_status => x_return_status,
1134 p_package_name => g_pkg_name,
1135 p_procedure_name => l_api_name,
1136 p_before_after => 'B'
1137 );
1138 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
1139 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1140 ElsIf (x_return_status = OKC_API.G_RET_STS_ERROR) Then
1141 raise OKC_API.G_EXCEPTION_ERROR;
1142 End If;
1143 OKS_TEMPLATE_SET_PVT.update_row(p_api_version => l_api_version,
1144 p_init_msg_list => p_init_msg_list,
1145 x_return_status => x_return_status,
1146 x_msg_count => x_msg_count,
1147 x_msg_data => x_msg_data,
1148 p_tmsv_tbl => p_tmsv_tbl,
1149 x_tmsv_tbl => x_tmsv_tbl,
1150 px_error_tbl => px_error_tbl);
1151
1152
1153 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1154 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1155 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1156 raise OKC_API.G_EXCEPTION_ERROR;
1157 End If;
1158
1159 OKC_UTIL.call_user_hook
1160 (
1161 x_return_status => x_return_status,
1162 p_package_name => g_pkg_name,
1163 p_procedure_name => l_api_name,
1164 p_before_after => 'A'
1165 );
1166
1167 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1168 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1169 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1170 raise OKC_API.G_EXCEPTION_ERROR;
1171 End If;
1172
1173 x_return_status := l_return_status;
1174 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1175 EXCEPTION
1176 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1177 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1178 (
1179 l_api_name,
1180 G_PKG_NAME,
1181 'OKC_API.G_RET_STS_ERROR',
1182 x_msg_count,
1183 x_msg_data,
1184 '_PUB'
1185 );
1186 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1187 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1188 (
1189 l_api_name,
1190 G_PKG_NAME,
1191 'OKC_API.G_RET_STS_UNEXP_ERROR',
1192 x_msg_count,
1193 x_msg_data,
1194 '_PUB'
1195 );
1196 WHEN OTHERS THEN
1197 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1198 (
1199 l_api_name,
1200 G_PKG_NAME,
1201 'OTHERS',
1202 x_msg_count,
1203 x_msg_data,
1204 '_PUB'
1205 );
1206 END update_row;
1207
1208 ----------------------------------------
1209 -- PL/SQL TBL update_row for:TMSV_TBL --
1210 ----------------------------------------
1211 PROCEDURE update_row(
1212 p_api_version IN NUMBER,
1213 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1214 x_return_status OUT NOCOPY VARCHAR2,
1215 x_msg_count OUT NOCOPY NUMBER,
1216 x_msg_data OUT NOCOPY VARCHAR2,
1217 p_tmsv_tbl IN tmsv_tbl_type,
1218 x_tmsv_tbl OUT NOCOPY tmsv_tbl_type) IS
1219
1220 l_api_version CONSTANT NUMBER := 1;
1221 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1222 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1223 BEGIN
1224 OKC_API.init_msg_list(p_init_msg_list);
1225 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1226 G_PKG_NAME,
1227 p_init_msg_list,
1228 l_api_version,
1229 p_api_version,
1230 '_PUB',
1231 x_return_status);
1232 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1233 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1234 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1235 RAISE OKC_API.G_EXCEPTION_ERROR;
1236 END IF;
1237
1238 OKC_UTIL.call_user_hook
1239 (
1240 x_return_status => x_return_status,
1241 p_package_name => g_pkg_name,
1242 p_procedure_name => l_api_name,
1243 p_before_after => 'B'
1244 );
1245 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
1246 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1247 ElsIf (x_return_status = OKC_API.G_RET_STS_ERROR) Then
1248 raise OKC_API.G_EXCEPTION_ERROR;
1249 End If;
1250 OKS_TEMPLATE_SET_PVT.update_row(p_api_version => l_api_version,
1251 p_init_msg_list => p_init_msg_list,
1252 x_return_status => x_return_status,
1253 x_msg_count => x_msg_count,
1254 x_msg_data => x_msg_data,
1255 p_tmsv_tbl => p_tmsv_tbl,
1256 x_tmsv_tbl => x_tmsv_tbl);
1257
1258
1259 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1260 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1261 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1262 raise OKC_API.G_EXCEPTION_ERROR;
1263 End If;
1264
1265 OKC_UTIL.call_user_hook
1266 (
1267 x_return_status => x_return_status,
1268 p_package_name => g_pkg_name,
1269 p_procedure_name => l_api_name,
1270 p_before_after => 'A'
1271 );
1272
1273 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1274 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1275 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1276 raise OKC_API.G_EXCEPTION_ERROR;
1277 End If;
1278
1279 x_return_status := l_return_status;
1280 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1281 EXCEPTION
1282 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1283 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1284 (
1285 l_api_name,
1286 G_PKG_NAME,
1287 'OKC_API.G_RET_STS_ERROR',
1288 x_msg_count,
1289 x_msg_data,
1290 '_PUB'
1291 );
1292 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1293 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1294 (
1295 l_api_name,
1296 G_PKG_NAME,
1297 'OKC_API.G_RET_STS_UNEXP_ERROR',
1298 x_msg_count,
1299 x_msg_data,
1300 '_PUB'
1301 );
1302 WHEN OTHERS THEN
1303 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1304 (
1305 l_api_name,
1306 G_PKG_NAME,
1307 'OTHERS',
1308 x_msg_count,
1309 x_msg_data,
1310 '_PUB'
1311 );
1312 END update_row;
1313
1314 ---------------------------------------------------------------------------
1315 -- PROCEDURE delete_row
1316 ---------------------------------------------------------------------------
1317 ---------------------------------------
1318 -- delete_row for:OKS_TEMPLATE_SET_V --
1319 ---------------------------------------
1320 PROCEDURE delete_row(
1321 p_api_version IN NUMBER,
1322 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1323 x_return_status OUT NOCOPY VARCHAR2,
1324 x_msg_count OUT NOCOPY NUMBER,
1325 x_msg_data OUT NOCOPY VARCHAR2,
1326 p_tmsv_rec IN tmsv_rec_type) IS
1327
1328 l_api_version CONSTANT NUMBER := 1;
1329 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
1330 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1331 BEGIN
1332 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1333 G_PKG_NAME,
1334 p_init_msg_list,
1335 l_api_version,
1336 p_api_version,
1337 '_PUB',
1338 x_return_status);
1339 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1340 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1341 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1342 RAISE OKC_API.G_EXCEPTION_ERROR;
1343 END IF;
1344
1345 OKC_UTIL.call_user_hook
1346 (
1347 x_return_status => x_return_status,
1348 p_package_name => g_pkg_name,
1349 p_procedure_name => l_api_name,
1350 p_before_after => 'B'
1351 );
1352 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
1353 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1354 ElsIf (x_return_status = OKC_API.G_RET_STS_ERROR) Then
1355 raise OKC_API.G_EXCEPTION_ERROR;
1356 End If;
1357 OKS_TEMPLATE_SET_PVT.delete_row(p_api_version => l_api_version,
1358 p_init_msg_list => p_init_msg_list,
1359 x_return_status => x_return_status,
1360 x_msg_count => x_msg_count,
1361 x_msg_data => x_msg_data,
1362 p_tmsv_rec => p_tmsv_rec);
1363
1364
1365
1366 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1367 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1368 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1369 raise OKC_API.G_EXCEPTION_ERROR;
1370 End If;
1371
1372 OKC_UTIL.call_user_hook
1373 (
1374 x_return_status => x_return_status,
1375 p_package_name => g_pkg_name,
1376 p_procedure_name => l_api_name,
1377 p_before_after => 'A'
1378 );
1379
1380 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1381 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1382 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1383 raise OKC_API.G_EXCEPTION_ERROR;
1384 End If;
1385 x_return_status := l_return_status;
1386 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1387 EXCEPTION
1388 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1389 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1390 (
1391 l_api_name,
1392 G_PKG_NAME,
1393 'OKC_API.G_RET_STS_ERROR',
1394 x_msg_count,
1395 x_msg_data,
1396 '_PUB'
1397 );
1398 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1399 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1400 (
1401 l_api_name,
1402 G_PKG_NAME,
1403 'OKC_API.G_RET_STS_UNEXP_ERROR',
1404 x_msg_count,
1405 x_msg_data,
1406 '_PUB'
1407 );
1408 WHEN OTHERS THEN
1409 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1410 (
1411 l_api_name,
1412 G_PKG_NAME,
1413 'OTHERS',
1414 x_msg_count,
1415 x_msg_data,
1416 '_PUB'
1417 );
1418 END delete_row;
1419 --------------------------------------------------
1420 -- PL/SQL TBL delete_row for:OKS_TEMPLATE_SET_V --
1421 --------------------------------------------------
1422 PROCEDURE delete_row(
1423 p_api_version IN NUMBER,
1424 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1425 x_return_status OUT NOCOPY VARCHAR2,
1426 x_msg_count OUT NOCOPY NUMBER,
1427 x_msg_data OUT NOCOPY VARCHAR2,
1428 p_tmsv_tbl IN tmsv_tbl_type,
1429 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1430
1431 l_api_version CONSTANT NUMBER := 1;
1432 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
1433 l_return_status VARCHAR2(1);
1434 BEGIN
1435 OKC_API.init_msg_list(p_init_msg_list);
1436 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1437 G_PKG_NAME,
1438 p_init_msg_list,
1439 l_api_version,
1440 p_api_version,
1441 '_PUB',
1442 x_return_status);
1443 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1444 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1445 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1446 RAISE OKC_API.G_EXCEPTION_ERROR;
1447 END IF;
1448
1449 OKC_UTIL.call_user_hook
1450 (
1451 x_return_status => x_return_status,
1452 p_package_name => g_pkg_name,
1453 p_procedure_name => l_api_name,
1454 p_before_after => 'B'
1455 );
1456 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
1457 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1458 ElsIf (x_return_status = OKC_API.G_RET_STS_ERROR) Then
1459 raise OKC_API.G_EXCEPTION_ERROR;
1460 End If;
1461 OKS_TEMPLATE_SET_PVT.delete_row(p_api_version => l_api_version,
1462 p_init_msg_list => p_init_msg_list,
1463 x_return_status => x_return_status,
1464 x_msg_count => x_msg_count,
1465 x_msg_data => x_msg_data,
1466 p_tmsv_tbl => p_tmsv_tbl,
1467 px_error_tbl => px_error_tbl);
1468
1469
1470 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1471 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1472 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1473 raise OKC_API.G_EXCEPTION_ERROR;
1474 End If;
1475
1476 OKC_UTIL.call_user_hook
1477 (
1478 x_return_status => x_return_status,
1479 p_package_name => g_pkg_name,
1480 p_procedure_name => l_api_name,
1481 p_before_after => 'A'
1482 );
1483
1484 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1485 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1486 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1487 raise OKC_API.G_EXCEPTION_ERROR;
1488 End If;
1489
1490 x_return_status := l_return_status;
1491 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1492 EXCEPTION
1493 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1494 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1495 (
1496 l_api_name,
1497 G_PKG_NAME,
1498 'OKC_API.G_RET_STS_ERROR',
1499 x_msg_count,
1500 x_msg_data,
1501 '_PUB'
1502 );
1503 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1504 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1505 (
1506 l_api_name,
1507 G_PKG_NAME,
1508 'OKC_API.G_RET_STS_UNEXP_ERROR',
1509 x_msg_count,
1510 x_msg_data,
1511 '_PUB'
1512 );
1513 WHEN OTHERS THEN
1514 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1515 (
1516 l_api_name,
1517 G_PKG_NAME,
1518 'OTHERS',
1519 x_msg_count,
1520 x_msg_data,
1521 '_PUB'
1522 );
1523 END delete_row;
1524
1525 --------------------------------------------------
1526 -- PL/SQL TBL delete_row for:OKS_TEMPLATE_SET_V --
1527 --------------------------------------------------
1528 PROCEDURE delete_row(
1529 p_api_version IN NUMBER,
1530 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1531 x_return_status OUT NOCOPY VARCHAR2,
1532 x_msg_count OUT NOCOPY NUMBER,
1533 x_msg_data OUT NOCOPY VARCHAR2,
1534 p_tmsv_tbl IN tmsv_tbl_type) IS
1535
1536 l_api_version CONSTANT NUMBER := 1;
1537 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
1538 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1539 BEGIN
1540 OKC_API.init_msg_list(p_init_msg_list);
1541 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1542 G_PKG_NAME,
1543 p_init_msg_list,
1544 l_api_version,
1545 p_api_version,
1546 '_PUB',
1547 x_return_status);
1548 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1549 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1550 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1551 RAISE OKC_API.G_EXCEPTION_ERROR;
1552 END IF;
1553
1554 OKC_UTIL.call_user_hook
1555 (
1556 x_return_status => x_return_status,
1557 p_package_name => g_pkg_name,
1558 p_procedure_name => l_api_name,
1559 p_before_after => 'B'
1560 );
1561 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) Then
1562 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1563 ElsIf (x_return_status = OKC_API.G_RET_STS_ERROR) Then
1564 raise OKC_API.G_EXCEPTION_ERROR;
1565 End If;
1566 OKS_TEMPLATE_SET_PVT.delete_row(p_api_version => l_api_version,
1567 p_init_msg_list => p_init_msg_list,
1568 x_return_status => x_return_status,
1569 x_msg_count => x_msg_count,
1570 x_msg_data => x_msg_data,
1571 p_tmsv_tbl => p_tmsv_tbl);
1572
1573
1574
1575 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1576 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1577 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1578 raise OKC_API.G_EXCEPTION_ERROR;
1579 End If;
1580
1581 OKC_UTIL.call_user_hook
1582 (
1583 x_return_status => x_return_status,
1584 p_package_name => g_pkg_name,
1585 p_procedure_name => l_api_name,
1586 p_before_after => 'A'
1587 );
1588
1589 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1590 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1591 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1592 raise OKC_API.G_EXCEPTION_ERROR;
1593 End If;
1594
1595 x_return_status := l_return_status;
1596 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1597 EXCEPTION
1598 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1599 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1600 (
1601 l_api_name,
1602 G_PKG_NAME,
1603 'OKC_API.G_RET_STS_ERROR',
1604 x_msg_count,
1605 x_msg_data,
1606 '_PUB'
1607 );
1608 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1609 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1610 (
1611 l_api_name,
1612 G_PKG_NAME,
1613 'OKC_API.G_RET_STS_UNEXP_ERROR',
1614 x_msg_count,
1615 x_msg_data,
1616 '_PUB'
1617 );
1618 WHEN OTHERS THEN
1619 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1620 (
1621 l_api_name,
1622 G_PKG_NAME,
1623 'OTHERS',
1624 x_msg_count,
1625 x_msg_data,
1626 '_PUB'
1627 );
1628 END delete_row;
1629
1630 PROCEDURE get_template_set_dtls
1631 (
1632 p_api_version IN NUMBER,
1633 p_init_msg_list IN VARCHAR2,
1634 p_contract_id IN NUMBER,
1635 p_document_type IN VARCHAR2,
1636 p_report_duration IN NUMBER DEFAULT NULL,
1637 p_report_period IN VARCHAR2 DEFAULT NULL,
1638 x_template_language OUT NOCOPY VARCHAR2,
1639 x_message_template_id OUT NOCOPY NUMBER,
1640 x_attachment_template_id OUT NOCOPY NUMBER,
1641 x_attachment_name OUT NOCOPY VARCHAR2,
1642 x_contract_update_status OUT NOCOPY VARCHAR2,
1643 x_return_status OUT NOCOPY VARCHAR2,
1644 x_msg_data OUT NOCOPY VARCHAR2,
1645 x_msg_count OUT NOCOPY NUMBER
1646 ) AS
1647
1648 l_api_name CONSTANT VARCHAR2(30) := 'get_template_set_dtls';
1649 l_api_version CONSTANT NUMBER := 1;
1650 l_item_key wf_items.item_key%TYPE :='';
1651 l_online_yn VARCHAR2(1):= 'N';
1652 l_process_code OKS_REPORT_TEMPLATES_V.process_code%TYPE;
1653 l_applies_to OKS_REPORT_TEMPLATES_V.applies_to%TYPE;
1654 l_template_set_id OKS_REPORT_TEMPLATES_V.template_set_id%TYPE;
1655
1656
1657 CURSOR csr_renewal_k_yn IS
1658 SELECT DECODE(renewal_type_used,NULL,'N','R')
1659 FROM oks_k_headers_b
1660 WHERE chr_id = p_contract_id;
1661
1662 CURSOR csr_tmpl_set_dtls(p_template_set_id IN NUMBER,
1663 p_process_code IN VARCHAR2,
1664 p_applies_to IN VARCHAR2
1665 ) IS
1666 SELECT r.report_id,
1667 r.message_template_id,
1668 r.sts_code,
1669 r.attachment_name
1670 FROM oks_report_templates r
1671 WHERE r.template_set_id = p_template_set_id
1672 AND r.template_set_type= p_document_type
1673 AND ( DECODE(r.process_code,'B','B','X') = 'B' OR
1674 r.process_code = p_process_code)
1675 AND ( DECODE(r.applies_to,'B','B','X') = 'B' OR
1676 r.applies_to = p_applies_to)
1677 AND sysdate BETWEEN r.START_DATE AND NVL(r.end_date,SYSDATE+1)
1678 AND NVL(r.report_period,'X') = NVL(p_report_period, NVL(r.report_period,'X'))
1679 AND NVL(r.report_duration,-99) = NVL(p_report_duration, NVL(r.report_duration,-99)) ;
1680
1681 CURSOR csr_k_item_key IS
1682 SELECT wf_item_key
1683 FROM oks_k_headers_b
1684 WHERE chr_id = p_contract_id;
1685
1686 CURSOR csr_xdo_template_name(p_attachment_template_id IN NUMBER)IS
1687 SELECT template_name
1688 FROM xdo_templates_vl
1689 WHERE template_id=p_attachment_template_id;
1690
1691
1692 BEGIN
1693 -- start debug log
1694 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1695 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1696 G_MODULE||l_api_name,
1697 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1698 END IF;
1699
1700 -- Standard call to check for call compatibility.
1701 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1702 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1703 END IF;
1704
1705 -- Initialize message list if p_init_msg_list is set to TRUE.
1706 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1707 FND_MSG_PUB.initialize;
1708 END IF;
1709
1710 -- Initialize API return status to success
1711 x_return_status := FND_API.G_RET_STS_SUCCESS;
1712
1713 -- set moac context
1714 mo_global.init('OKC');
1715
1716 -- get the workflow key
1717 OPEN csr_k_item_key;
1718 FETCH csr_k_item_key INTO l_item_key;
1719 CLOSE csr_k_item_key;
1720
1721 -- check if the current contract is ONLINE or MANUAL
1722
1723 OKS_WF_K_PROCESS_PVT.is_online_k_yn
1724 (
1725 p_api_version => 1.0,
1726 p_init_msg_list => FND_API.G_FALSE,
1727 p_contract_id => p_contract_id ,
1728 p_item_key => l_item_key ,
1729 x_online_yn => l_online_yn ,
1730 x_return_status => x_return_status,
1731 x_msg_count => x_msg_count,
1732 x_msg_data => x_msg_data
1733 );
1734
1735 -- debug log
1736 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1737 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1738 G_MODULE||l_api_name,
1739 '150: After Calling OKS_WF_K_PROCESS_PVT.is_online_k_yn ');
1740 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1741 G_MODULE||l_api_name,
1742 '150: x_return_status '||x_return_status);
1743 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1744 G_MODULE||l_api_name,
1745 '150: l_online_yn '||l_online_yn);
1746 END IF;
1747 --- If any errors happen treat it as online K
1748 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1749 l_online_yn := 'Y';
1750 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1751 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1752 l_online_yn := 'Y';
1753 -- RAISE FND_API.G_EXC_ERROR;
1754 END IF;
1755
1756
1757
1758 IF l_online_yn = 'Y' THEN
1759 l_process_code := 'O';
1760 ELSE
1761 l_process_code := 'M';
1762 END IF;
1763
1764 -- check if contract is New or Renewal K
1765 OPEN csr_renewal_k_yn;
1766 FETCH csr_renewal_k_yn INTO l_applies_to;
1767 CLOSE csr_renewal_k_yn;
1768
1769 -- debug log
1770 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1771 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1772 G_MODULE||l_api_name,
1773 '200: l_applies_to '||l_applies_to);
1774 END IF;
1775
1776 -- debug log
1777 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1778 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1779 G_MODULE||l_api_name,
1780 '210: Calling OKS_RENEW_UTIL_PVT.GET_TEMPLATE_SET ');
1781 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1782 G_MODULE||l_api_name,
1783 '210: p_contract_id : '||p_contract_id);
1784 END IF;
1785
1786 -- get the template_set_id and template set language
1787
1788 OKS_RENEW_UTIL_PVT.get_template_set
1789 (
1790 p_api_version => 1.0,
1791 p_init_msg_list => 'F',
1792 p_chr_id => p_contract_id,
1793 x_template_set_id => l_template_set_id,
1794 x_template_lang => x_template_language,
1795 x_return_status => x_return_status,
1796 x_msg_count => x_msg_count,
1797 x_msg_data => x_msg_data
1798 );
1799
1800
1801 -- debug log
1802 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1803 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1804 G_MODULE||l_api_name,
1805 '250: After Calling OKS_RENEW_UTIL_PVT.GET_TEMPLATE_SET');
1806 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1807 G_MODULE||l_api_name,
1808 '250: l_template_set_id : '||l_template_set_id);
1809 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1810 G_MODULE||l_api_name,
1811 '250: x_template_language : '||x_template_language);
1812 END IF;
1813
1814 --- If any errors happen abort API
1815 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1816 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1817 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1818 RAISE FND_API.G_EXC_ERROR;
1819 END IF;
1820
1821 -- get the template details
1822 OPEN csr_tmpl_set_dtls(p_template_set_id => l_template_set_id,
1823 p_process_code => l_process_code,
1824 p_applies_to => l_applies_to
1825 );
1826 FETCH csr_tmpl_set_dtls INTO x_attachment_template_id,
1827 x_message_template_id,
1828 x_contract_update_status,
1829 x_attachment_name ;
1830 CLOSE csr_tmpl_set_dtls;
1831
1832 -- if attachment name is NULL then get template name from xdo_templates_vl
1833 IF x_attachment_name IS NULL THEN
1834 OPEN csr_xdo_template_name(p_attachment_template_id => x_attachment_template_id);
1835 FETCH csr_xdo_template_name INTO x_attachment_name;
1836 CLOSE csr_xdo_template_name;
1837 END IF;
1838
1839 -- debug log
1840 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1841 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1842 G_MODULE||l_api_name,
1843 '300: x_attachment_template_id : '||x_attachment_template_id);
1844 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1845 G_MODULE||l_api_name,
1846 '300: x_message_template_id : '||x_message_template_id);
1847 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1848 G_MODULE||l_api_name,
1849 '300: x_contract_update_status : '||x_contract_update_status);
1850 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1851 G_MODULE||l_api_name,
1852 '300: x_attachment_name : '||x_attachment_name);
1853 END IF;
1854
1855 -- Standard call to get message count and if count is 1, get message info.
1856 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1857
1858 -- end debug log
1859 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1860 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1861 G_MODULE||l_api_name,
1862 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1863 END IF;
1864
1865 EXCEPTION
1866 WHEN FND_API.G_EXC_ERROR THEN
1867 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1868 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1869 G_MODULE||l_api_name,
1870 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1871 END IF;
1872
1873 x_return_status := G_RET_STS_ERROR ;
1874 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1875
1876 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1877 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1878 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1879 G_MODULE||l_api_name,
1880 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1881 END IF;
1882
1883 x_return_status := G_RET_STS_UNEXP_ERROR ;
1884 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1885
1886 WHEN OTHERS THEN
1887 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1888 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1889 G_MODULE||l_api_name,
1890 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1891 END IF;
1892 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1893
1894 END get_template_set_dtls;
1895
1896
1897
1898
1899
1900
1901 END OKS_TEMPLATE_SET_PUB;