[Home] [Help]
PACKAGE BODY: APPS.OKL_TRX_EXTENSION_PVT
Source
1 PACKAGE BODY OKL_TRX_EXTENSION_PVT AS
2 /*$Header: OKLCTEHB.pls 120.2 2007/08/06 13:50:26 prasjain noship $*/
3
4 ----------------------------------------------------------------------------------
5 -- Start of comments
6 --
7 -- Procedure Name : create_trx_extension
8 -- Description : wrapper api for creating Transaction Extension
9 -- Business Rules :
10 -- Parameters : p_api_version ,p_init_msg_list,x_return_status
11 -- ,x_msg_count ,x_msg_data ,p_tehv_rec, p_telv_tbl
12 -- ,x_tehv_rec, x_telv_tbl
13 -- Version : 1.0
14 -- End of comments
15 ----------------------------------------------------------------------------------
16 PROCEDURE create_trx_extension(
17 p_api_version IN NUMBER
18 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
19 ,x_return_status OUT NOCOPY VARCHAR2
20 ,x_msg_count OUT NOCOPY NUMBER
21 ,x_msg_data OUT NOCOPY VARCHAR2
22 ,p_tehv_rec IN tehv_rec_type
23 ,p_telv_tbl IN telv_tbl_type
24 ,x_tehv_rec OUT NOCOPY tehv_rec_type
25 ,x_telv_tbl OUT NOCOPY telv_tbl_type
26 ) IS
27
28 i NUMBER;
29 l_tehv_rec tehv_rec_type;
30 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
31 l_telv_tbl telv_tbl_type := p_telv_tbl;
32
33 BEGIN
34 -- Populate TRX EXTENSION
35 create_trx_extension(
36 p_api_version => p_api_version
37 ,p_init_msg_list => p_init_msg_list
38 ,x_return_status => x_return_status
39 ,x_msg_count => x_msg_count
40 ,x_msg_data => x_msg_data
41 ,p_tehv_rec => p_tehv_rec
42 ,x_tehv_rec => x_tehv_rec);
43
44 IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
45
46 -- populate the foreign key for the detail
47 IF (l_telv_tbl.COUNT > 0) THEN
48 i:= l_telv_tbl.FIRST;
49 LOOP
50 l_telv_tbl(i).teh_id := x_tehv_rec.header_extension_id;
51
52 EXIT WHEN(i = l_telv_tbl.LAST);
53 i := l_telv_tbl.NEXT(i);
54 END LOOP;
55 END IF;
56
57
58 -- populate the detail
59 create_txl_extension(
60 p_api_version => p_api_version
61 ,p_init_msg_list => p_init_msg_list
62 ,x_return_status => x_return_status
63 ,x_msg_count => x_msg_count
64 ,x_msg_data => x_msg_data
65 ,p_telv_tbl => l_telv_tbl
66 ,x_telv_tbl => x_telv_tbl);
67 END IF;
68
69 EXCEPTION
70 WHEN G_EXCEPTION_HALT_VALIDATION THEN
71 NULL;
72
73 WHEN OTHERS THEN
74 OKC_API.SET_MESSAGE(p_app_name => g_app_name
75 ,p_msg_name => g_unexpected_error
76 ,p_token1 => g_sqlcode_token
77 ,p_token1_value => sqlcode
78 ,p_token2 => g_sqlerrm_token
79 ,p_token2_value => sqlerrm);
80 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
81 END create_trx_extension;
82
83 ----------------------------------------------------------------------------------
84 -- Start of comments
85 --
86 -- Procedure Name : create_trx_extension
87 -- Description : wrapper api for creating Transaction Extension Header
88 -- Business Rules :
89 -- Parameters : p_api_version, p_init_msg_list, x_return_status
90 -- ,x_msg_count, x_msg_data, p_tehv_rec, x_tehv_rec
91 -- Version : 1.0
92 -- End of comments
93 ----------------------------------------------------------------------------------
94 PROCEDURE create_trx_extension(
95 p_api_version IN NUMBER
96 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
97 ,x_return_status OUT NOCOPY VARCHAR2
98 ,x_msg_count OUT NOCOPY NUMBER
99 ,x_msg_data OUT NOCOPY VARCHAR2
100 ,p_tehv_rec IN tehv_rec_type
101 ,x_tehv_rec OUT NOCOPY tehv_rec_type) IS
102 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
103 BEGIN
104
105 okl_teh_pvt.insert_row(
106 p_api_version => p_api_version
107 ,p_init_msg_list => p_init_msg_list
108 ,x_return_status => x_return_status
109 ,x_msg_count => x_msg_count
110 ,x_msg_data => x_msg_data
111 ,p_tehv_rec => p_tehv_rec
112 ,x_tehv_rec => x_tehv_rec
113 );
114
115 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
116 RAISE G_EXCEPTION_HALT_VALIDATION;
117 ELSE
118 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
119 l_return_status := x_return_status;
120 END IF;
121 END IF;
122 -- Custom code if any
123
124 EXCEPTION
125 WHEN G_EXCEPTION_HALT_VALIDATION THEN
126 NULL;
127
128 WHEN OTHERS THEN
129 OKC_API.SET_MESSAGE(p_app_name => g_app_name
130 ,p_msg_name => g_unexpected_error
131 ,p_token1 => g_sqlcode_token
132 ,p_token1_value => sqlcode
133 ,p_token2 => g_sqlerrm_token
134 ,p_token2_value => sqlerrm);
135 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
136 END create_trx_extension;
137
138 ----------------------------------------------------------------------------------
139 -- Start of comments
140 --
141 -- Procedure Name : create_trx_extension
142 -- Description : wrapper api for creating a table of records for
143 -- Transaction Extension Header
144 -- Business Rules :
145 -- Parameters : p_api_version, p_init_msg_list, x_return_status
146 -- ,x_msg_count, x_msg_data, p_tehv_tbl, x_tehv_tbl
147 -- Version : 1.0
148 -- End of comments
149 ----------------------------------------------------------------------------------
150 PROCEDURE create_trx_extension(
151 p_api_version IN NUMBER
152 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
153 ,x_return_status OUT NOCOPY VARCHAR2
154 ,x_msg_count OUT NOCOPY NUMBER
155 ,x_msg_data OUT NOCOPY VARCHAR2
156 ,p_tehv_tbl IN tehv_tbl_type
157 ,x_tehv_tbl OUT NOCOPY tehv_tbl_type) IS
158 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
159 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
160 i NUMBER := 0;
161
162 BEGIN
163
164 Okc_Api.init_msg_list(p_init_msg_list);
165 -- Make sure PL/SQL table has records in it before passing
166 IF (p_tehv_tbl.COUNT > 0) THEN
167 i := p_tehv_tbl.FIRST;
168 LOOP
169 create_trx_extension(
170 p_api_version => p_api_version,
171 p_init_msg_list => Okc_Api.G_FALSE,
172 x_return_status => x_return_status,
173 x_msg_count => x_msg_count,
174 x_msg_data => x_msg_data,
175 p_tehv_rec => p_tehv_tbl(i),
176 x_tehv_rec => x_tehv_tbl(i));
177 -- store the highest degree of error
178 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
179 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
180 l_overall_status := x_return_status;
181 END IF;
182 END IF;
183
184 EXIT WHEN (i = p_tehv_tbl.LAST);
185 i := p_tehv_tbl.NEXT(i);
186 END LOOP;
187
188 END IF;
189
190 x_return_status := l_overall_status;
191
192
193 EXCEPTION
194 WHEN G_EXCEPTION_HALT_VALIDATION THEN
195 NULL;
196
197 WHEN OTHERS THEN
198 OKC_API.SET_MESSAGE(p_app_name => g_app_name
199 ,p_msg_name => g_unexpected_error
200 ,p_token1 => g_sqlcode_token
201 ,p_token1_value => sqlcode
202 ,p_token2 => g_sqlerrm_token
203 ,p_token2_value => sqlerrm);
204 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
205
206 END create_trx_extension;
207
208 ----------------------------------------------------------------------------------
209 -- Start of comments
210 --
211 -- Procedure Name : update_trx_extension
212 -- Description : wrapper api for updating Transaction Extension
213 -- Business Rules :
214 -- Parameters : p_api_version, p_init_msg_list, x_return_status
215 -- ,x_msg_count, x_msg_data, p_tehv_rec, p_telv_tbl,
216 -- ,x_tehv_rec, x_telv_tbl
217 -- Version : 1.0
218 -- End of comments
219 ----------------------------------------------------------------------------------
220 PROCEDURE update_trx_extension(
221 p_api_version IN NUMBER,
222 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
223 x_return_status OUT NOCOPY VARCHAR2,
224 x_msg_count OUT NOCOPY NUMBER,
225 x_msg_data OUT NOCOPY VARCHAR2,
226 p_tehv_rec IN tehv_rec_type,
227 p_telv_tbl IN telv_tbl_type,
228 x_tehv_rec OUT NOCOPY tehv_rec_type,
229 x_telv_tbl OUT NOCOPY telv_tbl_type) IS
230 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
231 BEGIN
232 -- Update the master
233 update_trx_extension(
234 p_api_version => p_api_version
235 ,p_init_msg_list => p_init_msg_list
236 ,x_return_status => x_return_status
237 ,x_msg_count => x_msg_count
238 ,x_msg_data => x_msg_data
239 ,p_tehv_rec => p_tehv_rec
240 ,x_tehv_rec => x_tehv_rec
241 );
242
243 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
244
245 -- Update the detail
246 update_txl_extension(
247 p_api_version => p_api_version
248 ,p_init_msg_list => p_init_msg_list
249 ,x_return_status => x_return_status
250 ,x_msg_count => x_msg_count
251 ,x_msg_data => x_msg_data
252 ,p_telv_tbl => p_telv_tbl
253 ,x_telv_tbl => x_telv_tbl
254 );
255
256 END IF;
257
258
259 EXCEPTION
260 WHEN G_EXCEPTION_HALT_VALIDATION THEN
261 NULL;
262
263 WHEN OTHERS THEN
264 OKC_API.SET_MESSAGE(p_app_name => g_app_name
265 ,p_msg_name => g_unexpected_error
266 ,p_token1 => g_sqlcode_token
267 ,p_token1_value => sqlcode
268 ,p_token2 => g_sqlerrm_token
269 ,p_token2_value => sqlerrm);
270 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
271
272 END update_trx_extension;
273
274 ----------------------------------------------------------------------------------
275 -- Start of comments
276 --
277 -- Procedure Name : validate_trx_extension
278 -- Description : wrapper api for validating Transaction Extension
279 -- Business Rules :
280 -- Parameters : p_api_version, p_init_msg_list, x_return_status
281 -- ,x_msg_count, x_msg_data, p_tehv_rec, p_telv_tbl
282 -- Version : 1.0
283 -- End of comments
284 ----------------------------------------------------------------------------------
285 PROCEDURE validate_trx_extension(
286 p_api_version IN NUMBER
287 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
288 ,x_return_status OUT NOCOPY VARCHAR2
289 ,x_msg_count OUT NOCOPY NUMBER
290 ,x_msg_data OUT NOCOPY VARCHAR2
291 ,p_tehv_rec IN tehv_rec_type
292 ,p_telv_tbl IN telv_tbl_type) IS
293
294 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
295 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
296
297 BEGIN
298 -- Validate the master
299 validate_trx_extension(
300 p_api_version => p_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_tehv_rec => p_tehv_rec
306 );
307
308 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
309 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
310 l_overall_status := x_return_status;
311 END IF;
312 END IF;
313
314 -- Validate the detail
315 validate_txl_extension(
316 p_api_version => p_api_version
317 ,p_init_msg_list => p_init_msg_list
318 ,x_return_status => x_return_status
319 ,x_msg_count => x_msg_count
320 ,x_msg_data => x_msg_data
321 ,p_telv_tbl => p_telv_tbl
322 );
323
324 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
325 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
326 l_overall_status := x_return_status;
327 END IF;
328 END IF;
329
330 x_return_status := l_overall_status;
331
332 EXCEPTION
333 WHEN G_EXCEPTION_HALT_VALIDATION THEN
334 NULL;
335 WHEN OTHERS THEN
336 OKC_API.SET_MESSAGE(p_app_name => g_app_name
337 ,p_msg_name => g_unexpected_error
338 ,p_token1 => g_sqlcode_token
339 ,p_token1_value => sqlcode
340 ,p_token2 => g_sqlerrm_token
341 ,p_token2_value => sqlerrm);
342 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
343
344 END validate_trx_extension;
345
346
347 ----------------------------------------------------------------------------------
348 -- Start of comments
349 --
350 -- Procedure Name : lock_trx_extension
351 -- Description : wrapper api for locking a record
352 -- Business Rules :
353 -- Parameters : p_api_version, p_init_msg_list, x_return_status
354 -- ,x_msg_count, x_msg_data, p_tehv_rec
355 -- Version : 1.0
356 -- End of comments
357 ----------------------------------------------------------------------------------
358 PROCEDURE lock_trx_extension(
359 p_api_version IN NUMBER
360 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
361 ,x_return_status OUT NOCOPY VARCHAR2
362 ,x_msg_count OUT NOCOPY NUMBER
363 ,x_msg_data OUT NOCOPY VARCHAR2
364 ,p_tehv_rec IN tehv_rec_type) IS
365 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
366
367 BEGIN
368 okl_teh_pvt.lock_row(
369 p_api_version => p_api_version
370 ,p_init_msg_list => p_init_msg_list
371 ,x_return_status => x_return_status
372 ,x_msg_count => x_msg_count
373 ,x_msg_data => x_msg_data
374 ,p_tehv_rec => p_tehv_rec
375 );
376 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
377 RAISE G_EXCEPTION_HALT_VALIDATION;
378 ELSE
379 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
380 l_return_status := x_return_status;
381 END IF;
382 END IF;
383
384 EXCEPTION
385 WHEN G_EXCEPTION_HALT_VALIDATION THEN
386 NULL;
387 WHEN OTHERS THEN
388 OKC_API.SET_MESSAGE(p_app_name => g_app_name
389 ,p_msg_name => g_unexpected_error
390 ,p_token1 => g_sqlcode_token
391 ,p_token1_value => sqlcode
392 ,p_token2 => g_sqlerrm_token
393 ,p_token2_value => sqlerrm);
394 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
395 END lock_trx_extension;
396
397 ----------------------------------------------------------------------------------
398 -- Start of comments
399 --
400 -- Procedure Name : lock_trx_extension
401 -- Description : wrapper api for locking a table of records
402 -- Business Rules :
403 -- Parameters : p_api_version, p_init_msg_list, x_return_status
404 -- ,x_msg_count, x_msg_data, p_tehv_tbl
405 -- Version : 1.0
406 -- End of comments
407 ----------------------------------------------------------------------------------
408 PROCEDURE lock_trx_extension(
409 p_api_version IN NUMBER
410 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
411 ,x_return_status OUT NOCOPY VARCHAR2
412 ,x_msg_count OUT NOCOPY NUMBER
413 ,x_msg_data OUT NOCOPY VARCHAR2
414 ,p_tehv_tbl IN tehv_tbl_type) IS
415
416 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
417 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
418 i NUMBER := 0;
419
420 BEGIN
421
422 Okc_Api.init_msg_list(p_init_msg_list);
423 -- Make sure PL/SQL table has records in it before passing
424 IF (p_tehv_tbl.COUNT > 0) THEN
425 i := p_tehv_tbl.FIRST;
426 LOOP
427 lock_trx_extension(
428 p_api_version => p_api_version,
429 p_init_msg_list => Okc_Api.G_FALSE,
430 x_return_status => x_return_status,
431 x_msg_count => x_msg_count,
432 x_msg_data => x_msg_data,
433 p_tehv_rec => p_tehv_tbl(i));
434
435 -- store the highest degree of error
436 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
437 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
438 l_overall_status := x_return_status;
439 END IF;
440 END IF;
441
442 EXIT WHEN (i = p_tehv_tbl.LAST);
443 i := p_tehv_tbl.NEXT(i);
444 END LOOP;
445
446 END IF;
447
448 x_return_status := l_overall_status;
449
450 EXCEPTION
451 WHEN G_EXCEPTION_HALT_VALIDATION THEN
452 NULL;
453 WHEN OTHERS THEN
454 OKC_API.SET_MESSAGE(p_app_name => g_app_name
455 ,p_msg_name => g_unexpected_error
456 ,p_token1 => g_sqlcode_token
457 ,p_token1_value => sqlcode
458 ,p_token2 => g_sqlerrm_token
459 ,p_token2_value => sqlerrm);
460 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
461 END lock_trx_extension;
462
463 ----------------------------------------------------------------------------------
464 -- Start of comments
465 --
466 -- Procedure Name : update_trx_extension
467 -- Description : wrapper api for updating a Header record
468 -- Business Rules :
469 -- Parameters : p_api_version, p_init_msg_list, x_return_status
470 -- ,x_msg_count, x_msg_data, p_tehv_rec, x_tehv_rec
471 -- Version : 1.0
472 -- End of comments
473 ----------------------------------------------------------------------------------
474 PROCEDURE update_trx_extension(
475 p_api_version IN NUMBER
476 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
477 ,x_return_status OUT NOCOPY VARCHAR2
478 ,x_msg_count OUT NOCOPY NUMBER
479 ,x_msg_data OUT NOCOPY VARCHAR2
480 ,p_tehv_rec IN tehv_rec_type
481 ,x_tehv_rec OUT NOCOPY tehv_rec_type) IS
482 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
483 BEGIN
484 okl_teh_pvt.update_row(
485 p_api_version => p_api_version
486 ,p_init_msg_list => p_init_msg_list
487 ,x_return_status => x_return_status
488 ,x_msg_count => x_msg_count
489 ,x_msg_data => x_msg_data
490 ,p_tehv_rec => p_tehv_rec
491 ,x_tehv_rec => x_tehv_rec
492 );
493 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
494 RAISE G_EXCEPTION_HALT_VALIDATION;
495 ELSE
496 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
497 l_return_status := x_return_status;
498 END IF;
499 END IF;
500 EXCEPTION
501 WHEN G_EXCEPTION_HALT_VALIDATION THEN
502 NULL;
503 WHEN OTHERS THEN
504 OKC_API.SET_MESSAGE(p_app_name => g_app_name
505 ,p_msg_name => g_unexpected_error
506 ,p_token1 => g_sqlcode_token
507 ,p_token1_value => sqlcode
508 ,p_token2 => g_sqlerrm_token
509 ,p_token2_value => sqlerrm);
510 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
511 END update_trx_extension;
512
513 ----------------------------------------------------------------------------------
514 -- Start of comments
515 --
516 -- Procedure Name : update_trx_extension
517 -- Description : wrapper api for updating a table of Header records
518 -- Business Rules :
519 -- Parameters : p_api_version, p_init_msg_list, x_return_status
520 -- ,x_msg_count, x_msg_data, p_tehv_tbl, x_tehv_tbl
521 -- Version : 1.0
522 -- End of comments
523 ----------------------------------------------------------------------------------
524 PROCEDURE update_trx_extension(
525 p_api_version IN NUMBER
526 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
527 ,x_return_status OUT NOCOPY VARCHAR2
528 ,x_msg_count OUT NOCOPY NUMBER
529 ,x_msg_data OUT NOCOPY VARCHAR2
530 ,p_tehv_tbl IN tehv_tbl_type
531 ,x_tehv_tbl OUT NOCOPY tehv_tbl_type) IS
532 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
533 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
534 i NUMBER := 0;
535
536 BEGIN
537
538 Okc_Api.init_msg_list(p_init_msg_list);
539 -- Make sure PL/SQL table has records in it before passing
540 IF (p_tehv_tbl.COUNT > 0) THEN
541 i := p_tehv_tbl.FIRST;
542 LOOP
543 update_trx_extension(
544 p_api_version => p_api_version,
545 p_init_msg_list => Okc_Api.G_FALSE,
546 x_return_status => x_return_status,
547 x_msg_count => x_msg_count,
548 x_msg_data => x_msg_data,
549 p_tehv_rec => p_tehv_tbl(i),
550 x_tehv_rec => x_tehv_tbl(i));
551
552 -- store the highest degree of error
553 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
554 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
555 l_overall_status := x_return_status;
556 END IF;
557 END IF;
558
559 EXIT WHEN (i = p_tehv_tbl.LAST);
560 i := p_tehv_tbl.NEXT(i);
561 END LOOP;
562
563 END IF;
564
565 x_return_status := l_overall_status;
566
567
568 EXCEPTION
569 WHEN G_EXCEPTION_HALT_VALIDATION THEN
570 NULL;
571 WHEN OTHERS THEN
572 OKC_API.SET_MESSAGE(p_app_name => g_app_name
573 ,p_msg_name => g_unexpected_error
574 ,p_token1 => g_sqlcode_token
575 ,p_token1_value => sqlcode
576 ,p_token2 => g_sqlerrm_token
577 ,p_token2_value => sqlerrm);
578 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
579 END update_trx_extension;
580
581 ----------------------------------------------------------------------------------
582 -- Start of comments
583 --
584 -- Procedure Name : delete_trx_extension
585 -- Description : wrapper api for deleting a record
586 -- Business Rules :
587 -- Parameters : p_api_version, p_init_msg_list, x_return_status
588 -- ,x_msg_count, x_msg_data, p_tehv_rec
589 -- Version : 1.0
590 -- End of comments
591 ----------------------------------------------------------------------------------
592 PROCEDURE delete_trx_extension(
593 p_api_version IN NUMBER
594 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
595 ,x_return_status OUT NOCOPY VARCHAR2
596 ,x_msg_count OUT NOCOPY NUMBER
597 ,x_msg_data OUT NOCOPY VARCHAR2
598 ,p_tehv_rec IN tehv_rec_type) IS
599 i NUMBER :=0;
600 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
601 l_telv_tbl telv_tbl_type;
602
603 CURSOR tel_csr IS
604 SELECT tel.line_extension_id
605 FROM OKL_TXL_EXTENSION_B tel
606 WHERE tel.teh_id = p_tehv_rec.header_extension_id;
607 BEGIN
608 FOR tel_rec IN tel_csr
609 LOOP
610 i := i + 1;
611 l_telv_tbl(i).line_extension_id := tel_rec.line_extension_id;
612 END LOOP;
613
614 delete_txl_extension( p_api_version => p_api_version
615 ,p_init_msg_list => p_init_msg_list
616 ,x_return_status => x_return_status
617 ,x_msg_count => x_msg_count
618 ,x_msg_data => x_msg_data
619 ,p_telv_tbl => l_telv_tbl);
620
621
622 IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
623 okl_teh_pvt.delete_row(p_api_version => p_api_version
624 ,p_init_msg_list => p_init_msg_list
625 ,x_return_status => x_return_status
626 ,x_msg_count => x_msg_count
627 ,x_msg_data => x_msg_data
628 ,p_tehv_rec => p_tehv_rec);
629
630 END IF;
631
632
633 EXCEPTION
634 WHEN G_EXCEPTION_HALT_VALIDATION THEN
635 NULL;
636 WHEN OTHERS THEN
637 OKC_API.SET_MESSAGE(p_app_name => g_app_name
638 ,p_msg_name => g_unexpected_error
639 ,p_token1 => g_sqlcode_token
640 ,p_token1_value => sqlcode
641 ,p_token2 => g_sqlerrm_token
642 ,p_token2_value => sqlerrm);
643
644 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
645 END delete_trx_extension;
646
647
648 ----------------------------------------------------------------------------------
649 -- Start of comments
650 --
651 -- Procedure Name : delete_trx_extension
652 -- Description : wrapper api for deleting a table of records
653 -- Business Rules :
654 -- Parameters : p_api_version, p_init_msg_list, x_return_status
655 -- ,x_msg_count, x_msg_data, p_tehv_tbl
656 -- Version : 1.0
657 -- End of comments
658 ----------------------------------------------------------------------------------
659 PROCEDURE delete_trx_extension(
660 p_api_version IN NUMBER
661 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
662 ,x_return_status OUT NOCOPY VARCHAR2
663 ,x_msg_count OUT NOCOPY NUMBER
664 ,x_msg_data OUT NOCOPY VARCHAR2
665 ,p_tehv_tbl IN tehv_tbl_type) IS
666 i NUMBER :=0;
667 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
668 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
669
670 BEGIN
671 --Initialize the return status
672 x_return_status := OKC_API.G_RET_STS_SUCCESS;
673 IF (p_tehv_tbl.COUNT > 0) THEN
674 i := p_tehv_tbl.FIRST;
675 LOOP
676 delete_trx_extension(
677 p_api_version => p_api_version
678 ,p_init_msg_list => p_init_msg_list
679 ,x_return_status => x_return_status
680 ,x_msg_count => x_msg_count
681 ,x_msg_data => x_msg_data
682 ,p_tehv_rec => p_tehv_tbl(i)
683 );
684
685 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
686 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
687 l_overall_status := x_return_status;
688 END IF;
689 END IF;
690
691 EXIT WHEN (i = p_tehv_tbl.LAST);
692 i := p_tehv_tbl.NEXT(i);
693 END LOOP;
694
695 END IF;
696
697 x_return_status := l_overall_status;
698
699 EXCEPTION
700 WHEN G_EXCEPTION_HALT_VALIDATION THEN
701 NULL;
702 WHEN OTHERS THEN
703 OKC_API.SET_MESSAGE(p_app_name => g_app_name
704 ,p_msg_name => g_unexpected_error
705 ,p_token1 => g_sqlcode_token
706 ,p_token1_value => sqlcode
707 ,p_token2 => g_sqlerrm_token
708 ,p_token2_value => sqlerrm);
709 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
710 END delete_trx_extension;
711
712 ----------------------------------------------------------------------------------
713 -- Start of comments
714 --
715 -- Procedure Name : validate_trx_extension
716 -- Description : wrapper api for validating a Header record
717 -- Business Rules :
718 -- Parameters : p_api_version, p_init_msg_list, x_return_status
719 -- ,x_msg_count, x_msg_data, p_tehv_rec
720 -- Version : 1.0
721 -- End of comments
722 ----------------------------------------------------------------------------------
723 PROCEDURE validate_trx_extension(
724 p_api_version IN NUMBER
725 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
726 ,x_return_status OUT NOCOPY VARCHAR2
727 ,x_msg_count OUT NOCOPY NUMBER
728 ,x_msg_data OUT NOCOPY VARCHAR2
729 ,p_tehv_rec IN tehv_rec_type) IS
730 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
731 BEGIN
732 okl_teh_pvt.validate_row(
733 p_api_version => p_api_version
734 ,p_init_msg_list => p_init_msg_list
735 ,x_return_status => x_return_status
736 ,x_msg_count => x_msg_count
737 ,x_msg_data => x_msg_data
738 ,p_tehv_rec => p_tehv_rec
739 );
740 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
741 RAISE G_EXCEPTION_HALT_VALIDATION;
742 ELSE
743 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
744 l_return_status := x_return_status;
745 END IF;
746 END IF;
747 EXCEPTION
748 WHEN G_EXCEPTION_HALT_VALIDATION THEN
749 NULL;
750 WHEN OTHERS THEN
751 OKC_API.SET_MESSAGE(p_app_name => g_app_name
752 ,p_msg_name => g_unexpected_error
753 ,p_token1 => g_sqlcode_token
754 ,p_token1_value => sqlcode
755 ,p_token2 => g_sqlerrm_token
756 ,p_token2_value => sqlerrm);
757 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
758 END validate_trx_extension;
759
760 ----------------------------------------------------------------------------------
761 -- Start of comments
762 --
763 -- Procedure Name : validate_trx_extension
764 -- Description : wrapper api for validating a table of Header records
765 -- Business Rules :
766 -- Parameters : p_api_version, p_init_msg_list, x_return_status
767 -- ,x_msg_count, x_msg_data, p_tehv_tbl
768 -- Version : 1.0
769 -- End of comments
770 ----------------------------------------------------------------------------------
771 PROCEDURE validate_trx_extension(
772 p_api_version IN NUMBER,
773 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
774 x_return_status OUT NOCOPY VARCHAR2,
775 x_msg_count OUT NOCOPY NUMBER,
776 x_msg_data OUT NOCOPY VARCHAR2,
777 p_tehv_tbl IN tehv_tbl_type) IS
778 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
779 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
780 i NUMBER := 0;
781
782 BEGIN
783
784 Okc_Api.init_msg_list(p_init_msg_list);
785 -- Make sure PL/SQL table has records in it before passing
786 IF (p_tehv_tbl.COUNT > 0) THEN
787 i := p_tehv_tbl.FIRST;
788 LOOP
789 validate_trx_extension(
790 p_api_version => p_api_version,
791 p_init_msg_list => Okc_Api.G_FALSE,
792 x_return_status => x_return_status,
793 x_msg_count => x_msg_count,
794 x_msg_data => x_msg_data,
795 p_tehv_rec => p_tehv_tbl(i));
796
797 -- store the highest degree of error
798 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
799 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
800 l_overall_status := x_return_status;
801 END IF;
802 END IF;
803
804 EXIT WHEN (i = p_tehv_tbl.LAST);
805 i := p_tehv_tbl.NEXT(i);
806 END LOOP;
807
808 END IF;
809
810 x_return_status := l_overall_status;
811
812
813 EXCEPTION
814 WHEN G_EXCEPTION_HALT_VALIDATION THEN
815 NULL;
816 WHEN OTHERS THEN
817 OKC_API.SET_MESSAGE(p_app_name => g_app_name
818 ,p_msg_name => g_unexpected_error
819 ,p_token1 => g_sqlcode_token
820 ,p_token1_value => sqlcode
821 ,p_token2 => g_sqlerrm_token
822 ,p_token2_value => sqlerrm);
823 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
824 END validate_trx_extension;
825
826 ----------------------------------------------------------------------------------
827 -- Start of comments
828 --
829 -- Procedure Name : create_txl_extension
830 -- Description : wrapper api for creation of Transaction Extension Line
831 -- Business Rules :
832 -- Parameters : p_api_version, p_init_msg_list, x_return_status
833 -- ,x_msg_count, x_msg_data, p_telv_rec, x_telv_rec
834 -- Version : 1.0
835 -- End of comments
836 ----------------------------------------------------------------------------------
837 PROCEDURE create_txl_extension(
838 p_api_version IN NUMBER
839 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
840 ,x_return_status OUT NOCOPY VARCHAR2
841 ,x_msg_count OUT NOCOPY NUMBER
842 ,x_msg_data OUT NOCOPY VARCHAR2
843 ,p_telv_rec IN telv_rec_type
844 ,x_telv_rec OUT NOCOPY telv_rec_type) IS
845 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
846 BEGIN
847 okl_tel_pvt.insert_row(
848 p_api_version => p_api_version
849 ,p_init_msg_list => p_init_msg_list
850 ,x_return_status => x_return_status
851 ,x_msg_count => x_msg_count
852 ,x_msg_data => x_msg_data
853 ,p_telv_rec => p_telv_rec
854 ,x_telv_rec => x_telv_rec
855 );
856 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
857 RAISE G_EXCEPTION_HALT_VALIDATION;
858 ELSE
859 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
860 l_return_status := x_return_status;
861 END IF;
862 END IF;
863 EXCEPTION
864 WHEN G_EXCEPTION_HALT_VALIDATION THEN
865 NULL;
866 WHEN OTHERS THEN
867 OKC_API.SET_MESSAGE(p_app_name => g_app_name
868 ,p_msg_name => g_unexpected_error
869 ,p_token1 => g_sqlcode_token
870 ,p_token1_value => sqlcode
871 ,p_token2 => g_sqlerrm_token
872 ,p_token2_value => sqlerrm);
873 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
874 END create_txl_extension;
875
876 ----------------------------------------------------------------------------------
877 -- Start of comments
878 --
879 -- Procedure Name : create_txl_extension
880 -- Description : wrapper api for creation of multiple records of
881 -- Transaction Extension Line
882 -- Business Rules :
883 -- Parameters : p_api_version, p_init_msg_list, x_return_status
884 -- ,x_msg_count, x_msg_data, p_telv_tbl, x_telv_tbl
885 -- Version : 1.0
886 -- End of comments
887 ----------------------------------------------------------------------------------
888 PROCEDURE create_txl_extension(
889 p_api_version IN NUMBER
890 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
891 ,x_return_status OUT NOCOPY VARCHAR2
892 ,x_msg_count OUT NOCOPY NUMBER
893 ,x_msg_data OUT NOCOPY VARCHAR2
894 ,p_telv_tbl IN telv_tbl_type
895 ,x_telv_tbl OUT NOCOPY telv_tbl_type) IS
896 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
897 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
898 i NUMBER := 0;
899
900 BEGIN
901
902 OKC_API.init_msg_list(p_init_msg_list);
903 -- Make sure PL/SQL table has records in it before passing
904 IF (p_telv_tbl.COUNT > 0) THEN
905 i := p_telv_tbl.FIRST;
906 LOOP
907 create_txl_extension(
908 p_api_version => p_api_version,
909 p_init_msg_list => OKC_API.G_FALSE,
910 x_return_status => x_return_status,
911 x_msg_count => x_msg_count,
912 x_msg_data => x_msg_data,
913 p_telv_rec => p_telv_tbl(i),
914 x_telv_rec => x_telv_tbl(i));
915
916 -- store the highest degree of error
917 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
918 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
919 l_overall_status := x_return_status;
920 END IF;
921 END IF;
922
923 EXIT WHEN (i = p_telv_tbl.LAST);
924 i := p_telv_tbl.NEXT(i);
925 END LOOP;
926
927 END IF;
928
929 x_return_status := l_overall_status;
930
931 EXCEPTION
932 WHEN G_EXCEPTION_HALT_VALIDATION THEN
933 NULL;
934 WHEN OTHERS THEN
935 OKC_API.SET_MESSAGE(p_app_name => g_app_name
936 ,p_msg_name => g_unexpected_error
937 ,p_token1 => g_sqlcode_token
938 ,p_token1_value => sqlcode
939 ,p_token2 => g_sqlerrm_token
940 ,p_token2_value => sqlerrm);
941 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
942 END create_txl_extension;
943
944
945 ----------------------------------------------------------------------------------
946 -- Start of comments
947 --
948 -- Procedure Name : lock_txl_extension
949 -- Description : wrapper api for locking a record
950 -- Business Rules :
951 -- Parameters : p_api_version, p_init_msg_list, x_return_status
952 -- ,x_msg_count, x_msg_data, p_telv_rec
953 -- Version : 1.0
954 -- End of comments
955 ----------------------------------------------------------------------------------
956 PROCEDURE lock_txl_extension(
957 p_api_version IN NUMBER
958 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
959 ,x_return_status OUT NOCOPY VARCHAR2
960 ,x_msg_count OUT NOCOPY NUMBER
961 ,x_msg_data OUT NOCOPY VARCHAR2
962 ,p_telv_rec IN telv_rec_type) IS
963 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
964 BEGIN
965 okl_tel_pvt.lock_row(
966 p_api_version => p_api_version
967 ,p_init_msg_list => p_init_msg_list
968 ,x_return_status => x_return_status
969 ,x_msg_count => x_msg_count
970 ,x_msg_data => x_msg_data
971 ,p_telv_rec => p_telv_rec
972 );
973 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
974 RAISE G_EXCEPTION_HALT_VALIDATION;
975 ELSE
976 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
977 l_return_status := x_return_status;
978 END IF;
979 END IF;
980 EXCEPTION
981 WHEN G_EXCEPTION_HALT_VALIDATION THEN
982 NULL;
983 WHEN OTHERS THEN
984 OKC_API.SET_MESSAGE(p_app_name => g_app_name
985 ,p_msg_name => g_unexpected_error
986 ,p_token1 => g_sqlcode_token
987 ,p_token1_value => sqlcode
988 ,p_token2 => g_sqlerrm_token
989 ,p_token2_value => sqlerrm);
990 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
991
992 END lock_txl_extension;
993
994 ----------------------------------------------------------------------------------
995 -- Start of comments
996 --
997 -- Procedure Name : lock_txl_extension
998 -- Description : wrapper api for locking a table of records
999 -- Business Rules :
1000 -- Parameters : p_api_version, p_init_msg_list, x_return_status
1001 -- ,x_msg_count, x_msg_data, p_telv_tbl
1002 -- Version : 1.0
1003 -- End of comments
1004 ----------------------------------------------------------------------------------
1005 PROCEDURE lock_txl_extension(
1006 p_api_version IN NUMBER
1007 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1008 ,x_return_status OUT NOCOPY VARCHAR2
1009 ,x_msg_count OUT NOCOPY NUMBER
1010 ,x_msg_data OUT NOCOPY VARCHAR2
1011 ,p_telv_tbl IN telv_tbl_type) IS
1012 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1013 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1014 i NUMBER := 0;
1015
1016 BEGIN
1017
1018 OKC_API.init_msg_list(p_init_msg_list);
1019 -- Make sure PL/SQL table has records in it before passing
1020 IF (p_telv_tbl.COUNT > 0) THEN
1021 i := p_telv_tbl.FIRST;
1022 LOOP
1023 lock_txl_extension(
1024 p_api_version => p_api_version,
1025 p_init_msg_list => OKC_API.G_FALSE,
1026 x_return_status => x_return_status,
1027 x_msg_count => x_msg_count,
1028 x_msg_data => x_msg_data,
1029 p_telv_rec => p_telv_tbl(i));
1030
1031 -- store the highest degree of error
1032 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1033 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1034 l_overall_status := x_return_status;
1035 END IF;
1036 END IF;
1037
1038 EXIT WHEN (i = p_telv_tbl.LAST);
1039 i := p_telv_tbl.NEXT(i);
1040
1041 END LOOP;
1042
1043 END IF;
1044
1045 x_return_status := l_overall_status;
1046
1047
1048 EXCEPTION
1049 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1050 NULL;
1051 WHEN OTHERS THEN
1052 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1053 ,p_msg_name => g_unexpected_error
1054 ,p_token1 => g_sqlcode_token
1055 ,p_token1_value => sqlcode
1056 ,p_token2 => g_sqlerrm_token
1057 ,p_token2_value => sqlerrm);
1058 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1059 END lock_txl_extension;
1060
1061 ----------------------------------------------------------------------------------
1062 -- Start of comments
1063 --
1064 -- Procedure Name : update_txl_extension
1065 -- Description : wrapper api for updating a record
1066 -- Business Rules :
1067 -- Parameters : p_api_version, p_init_msg_list, x_return_status
1068 -- ,x_msg_count, x_msg_data, p_telv_rec, x_telv_rec
1069 -- Version : 1.0
1070 -- End of comments
1071 ----------------------------------------------------------------------------------
1072 PROCEDURE update_txl_extension(
1073 p_api_version IN NUMBER
1074 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1075 ,x_return_status OUT NOCOPY VARCHAR2
1076 ,x_msg_count OUT NOCOPY NUMBER
1077 ,x_msg_data OUT NOCOPY VARCHAR2
1078 ,p_telv_rec IN telv_rec_type
1079 ,x_telv_rec OUT NOCOPY telv_rec_type) IS
1080 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1081
1082 BEGIN
1083 okl_tel_pvt.update_row(
1084 p_api_version => p_api_version
1085 ,p_init_msg_list => p_init_msg_list
1086 ,x_return_status => x_return_status
1087 ,x_msg_count => x_msg_count
1088 ,x_msg_data => x_msg_data
1089 ,p_telv_rec => p_telv_rec
1090 ,x_telv_rec => x_telv_rec
1091 );
1092 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1093 RAISE G_EXCEPTION_HALT_VALIDATION;
1094 ELSE
1095 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1096 l_return_status := x_return_status;
1097 END IF;
1098 END IF;
1099 EXCEPTION
1100 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1101 NULL;
1102 WHEN OTHERS THEN
1103 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1104 ,p_msg_name => g_unexpected_error
1105 ,p_token1 => g_sqlcode_token
1106 ,p_token1_value => sqlcode
1107 ,p_token2 => g_sqlerrm_token
1108 ,p_token2_value => sqlerrm);
1109 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1110 END update_txl_extension;
1111
1112
1113 ----------------------------------------------------------------------------------
1114 -- Start of comments
1115 --
1116 -- Procedure Name : update_txl_extension
1117 -- Description : wrapper api for updating a table of records
1118 -- Business Rules :
1119 -- Parameters : p_api_version, p_init_msg_list, x_return_status
1120 -- ,x_msg_count, x_msg_data, p_telv_tbl, x_telv_tbl
1121 -- Version : 1.0
1122 -- End of comments
1123 ----------------------------------------------------------------------------------
1124 PROCEDURE update_txl_extension(
1125 p_api_version IN NUMBER
1126 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1127 ,x_return_status OUT NOCOPY VARCHAR2
1128 ,x_msg_count OUT NOCOPY NUMBER
1129 ,x_msg_data OUT NOCOPY VARCHAR2
1130 ,p_telv_tbl IN telv_tbl_type
1131 ,x_telv_tbl OUT NOCOPY telv_tbl_type) IS
1132
1133 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1134 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1135 i NUMBER := 0;
1136
1137 BEGIN
1138
1139 OKC_API.init_msg_list(p_init_msg_list);
1140 -- Make sure PL/SQL table has records in it before passing
1141 IF (p_telv_tbl.COUNT > 0) THEN
1142 i := p_telv_tbl.FIRST;
1143 LOOP
1144 update_txl_extension(
1145 p_api_version => p_api_version,
1146 p_init_msg_list => OKC_API.G_FALSE,
1147 x_return_status => x_return_status,
1148 x_msg_count => x_msg_count,
1149 x_msg_data => x_msg_data,
1150 p_telv_rec => p_telv_tbl(i),
1151 x_telv_rec => x_telv_tbl(i));
1152
1153 -- store the highest degree of error
1154 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1155 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1156 l_overall_status := x_return_status;
1157 END IF;
1158 END IF;
1159
1160 EXIT WHEN (i = p_telv_tbl.LAST);
1161 i := p_telv_tbl.NEXT(i);
1162 END LOOP;
1163
1164 END IF;
1165
1166 x_return_status := l_overall_status;
1167
1168
1169 EXCEPTION
1170 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1171 NULL;
1172 WHEN OTHERS THEN
1173 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1174 ,p_msg_name => g_unexpected_error
1175 ,p_token1 => g_sqlcode_token
1176 ,p_token1_value => sqlcode
1177 ,p_token2 => g_sqlerrm_token
1178 ,p_token2_value => sqlerrm);
1179 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1180 END update_txl_extension;
1181
1182 ----------------------------------------------------------------------------------
1183 -- Start of comments
1184 --
1185 -- Procedure Name : delete_txl_extension
1186 -- Description : wrapper api for deleting record
1187 -- Business Rules :
1188 -- Parameters : p_api_version, p_init_msg_list, x_return_status
1189 -- ,x_msg_count, x_msg_data, p_telv_rec
1190 -- Version : 1.0
1191 -- End of comments
1192 ----------------------------------------------------------------------------------
1193 PROCEDURE delete_txl_extension(
1194 p_api_version IN NUMBER
1195 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1196 ,x_return_status OUT NOCOPY VARCHAR2
1197 ,x_msg_count OUT NOCOPY NUMBER
1198 ,x_msg_data OUT NOCOPY VARCHAR2
1199 ,p_telv_rec IN telv_rec_type) IS
1200 i NUMBER :=0;
1201 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1202 BEGIN
1203 okl_tel_pvt.delete_row(
1204 p_api_version => p_api_version
1205 ,p_init_msg_list => p_init_msg_list
1206 ,x_return_status => x_return_status
1207 ,x_msg_count => x_msg_count
1208 ,x_msg_data => x_msg_data
1209 ,p_telv_rec => p_telv_rec);
1210 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1211 RAISE G_EXCEPTION_HALT_VALIDATION;
1212 ELSE
1213 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1214 l_return_status := x_return_status;
1215 END IF;
1216 END IF;
1217 EXCEPTION
1218 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1219 NULL;
1220 WHEN OTHERS THEN
1221 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1222 ,p_msg_name => g_unexpected_error
1223 ,p_token1 => g_sqlcode_token
1224 ,p_token1_value => sqlcode
1225 ,p_token2 => g_sqlerrm_token
1226 ,p_token2_value => sqlerrm);
1227 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1228 END delete_txl_extension;
1229
1230 ----------------------------------------------------------------------------------
1231 -- Start of comments
1232 --
1233 -- Procedure Name : delete_txl_extension
1234 -- Description : wrapper api for deleting a table of records
1235 -- Business Rules :
1236 -- Parameters : p_api_version, p_init_msg_list, x_return_status
1237 -- ,x_msg_count, x_msg_data, p_telv_tbl
1238 -- Version : 1.0
1239 -- End of comments
1240 ----------------------------------------------------------------------------------
1241 PROCEDURE delete_txl_extension(
1242 p_api_version IN NUMBER
1243 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1244 ,x_return_status OUT NOCOPY VARCHAR2
1245 ,x_msg_count OUT NOCOPY NUMBER
1246 ,x_msg_data OUT NOCOPY VARCHAR2
1247 ,p_telv_tbl IN telv_tbl_type) IS
1248
1249 i NUMBER :=0;
1250 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1251 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1252
1253 BEGIN
1254 --Initialize the return status
1255 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1256 IF (p_telv_tbl.COUNT > 0) THEN
1257 i := p_telv_tbl.FIRST;
1258 LOOP
1259 delete_txl_extension(
1260 p_api_version => p_api_version
1261 ,p_init_msg_list => p_init_msg_list
1262 ,x_return_status => x_return_status
1263 ,x_msg_count => x_msg_count
1264 ,x_msg_data => x_msg_data
1265 ,p_telv_rec => p_telv_tbl(i));
1266
1267 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1268 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1269 l_overall_status := x_return_status;
1270 END IF;
1271 END IF;
1272
1273 EXIT WHEN (i = p_telv_tbl.LAST);
1274 i := p_telv_tbl.NEXT(i);
1275 END LOOP;
1276
1277 END IF;
1278
1279 x_return_status := l_overall_status;
1280
1281 EXCEPTION
1282 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1283 NULL;
1284 WHEN OTHERS THEN
1285 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1286 ,p_msg_name => g_unexpected_error
1287 ,p_token1 => g_sqlcode_token
1288 ,p_token1_value => sqlcode
1289 ,p_token2 => g_sqlerrm_token
1290 ,p_token2_value => sqlerrm);
1291 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1292 END delete_txl_extension;
1293
1294 ----------------------------------------------------------------------------------
1295 -- Start of comments
1296 --
1297 -- Procedure Name : validate_txl_extension
1298 -- Description : wrapper api for validating a record
1299 -- Business Rules :
1300 -- Parameters : p_api_version, p_init_msg_list, x_return_status
1301 -- ,x_msg_count, x_msg_data, p_telv_rec
1302 -- Version : 1.0
1303 -- End of comments
1304 ----------------------------------------------------------------------------------
1305 PROCEDURE validate_txl_extension(
1306 p_api_version IN NUMBER
1307 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1308 ,x_return_status OUT NOCOPY VARCHAR2
1309 ,x_msg_count OUT NOCOPY NUMBER
1310 ,x_msg_data OUT NOCOPY VARCHAR2
1311 ,p_telv_rec IN telv_rec_type) IS
1312 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1313 BEGIN
1314 okl_tel_pvt.validate_row(
1315 p_api_version => p_api_version
1316 ,p_init_msg_list => p_init_msg_list
1317 ,x_return_status => x_return_status
1318 ,x_msg_count => x_msg_count
1319 ,x_msg_data => x_msg_data
1320 ,p_telv_rec => p_telv_rec
1321 );
1322 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1323 RAISE G_EXCEPTION_HALT_VALIDATION;
1324 ELSE
1325 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1326 l_return_status := x_return_status;
1327 END IF;
1328 END IF;
1329 EXCEPTION
1330 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1331 NULL;
1332 WHEN OTHERS THEN
1333 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1334 ,p_msg_name => g_unexpected_error
1335 ,p_token1 => g_sqlcode_token
1336 ,p_token1_value => sqlcode
1337 ,p_token2 => g_sqlerrm_token
1338 ,p_token2_value => sqlerrm);
1339 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1340 END validate_txl_extension;
1341
1342 ----------------------------------------------------------------------------------
1343 -- Start of comments
1344 --
1345 -- Procedure Name : validate_txl_extension
1346 -- Description : wrapper api for validating a table of records
1347 -- Business Rules :
1348 -- Parameters : p_api_version, p_init_msg_list, x_return_status
1349 -- ,x_msg_count, x_msg_data, p_telv_tbl
1350 -- Version : 1.0
1351 -- End of comments
1352 ----------------------------------------------------------------------------------
1353 PROCEDURE validate_txl_extension(
1354 p_api_version IN NUMBER
1355 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1356 ,x_return_status OUT NOCOPY VARCHAR2
1357 ,x_msg_count OUT NOCOPY NUMBER
1358 ,x_msg_data OUT NOCOPY VARCHAR2
1359 ,p_telv_tbl IN telv_tbl_type) IS
1360 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1361 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1362 i NUMBER := 0;
1363
1364 BEGIN
1365
1366 OKC_API.init_msg_list(p_init_msg_list);
1367 -- Make sure PL/SQL table has records in it before passing
1368 IF (p_telv_tbl.COUNT > 0) THEN
1369 i := p_telv_tbl.FIRST;
1370 LOOP
1371 validate_txl_extension(
1372 p_api_version => p_api_version,
1373 p_init_msg_list => OKC_API.G_FALSE,
1374 x_return_status => x_return_status,
1375 x_msg_count => x_msg_count,
1376 x_msg_data => x_msg_data,
1377 p_telv_rec => p_telv_tbl(i));
1378
1379 -- store the highest degree of error
1380 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1381 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1382 l_overall_status := x_return_status;
1383 END IF;
1384 END IF;
1385
1386 EXIT WHEN (i = p_telv_tbl.LAST);
1387 i := p_telv_tbl.NEXT(i);
1388 END LOOP;
1389
1390 x_return_status := l_overall_status;
1391
1392 END IF;
1393
1394
1395 EXCEPTION
1396 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1397 NULL;
1398 WHEN OTHERS THEN
1399 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1400 ,p_msg_name => g_unexpected_error
1401 ,p_token1 => g_sqlcode_token
1402 ,p_token1_value => sqlcode
1403 ,p_token2 => g_sqlerrm_token
1404 ,p_token2_value => sqlerrm);
1405 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1406 END validate_txl_extension;
1407
1408 ----------------------------------------------------------------------------------
1409 -- Start of comments
1410 --
1411 -- Added : PRASJAIN : Bug# 6268782
1412 --
1413 -- Procedure Name : create_trx_extension
1414 -- Description : wrapper api for creating Transaction Extension
1415 -- Business Rules :
1416 -- Parameters : p_api_version ,p_init_msg_list,x_return_status
1417 -- ,x_msg_count ,x_msg_data ,p_teh_rec, p_tehl_tbl
1418 -- ,x_teh_rec, x_tehl_tbl
1419 -- Version : 1.0
1420 -- End of comments
1421 ----------------------------------------------------------------------------------
1422 PROCEDURE create_trx_extension(
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_teh_rec IN teh_rec_type
1429 ,p_tehl_tbl IN tehl_tbl_type
1430 ,x_teh_rec OUT NOCOPY teh_rec_type
1431 ,x_tehl_tbl OUT NOCOPY tehl_tbl_type) IS
1432 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1433 BEGIN
1434 okl_teh_pvt.insert_row(
1435 p_api_version => p_api_version
1436 ,p_init_msg_list => p_init_msg_list
1437 ,x_return_status => x_return_status
1438 ,x_msg_count => x_msg_count
1439 ,x_msg_data => x_msg_data
1440 ,p_teh_rec => p_teh_rec
1441 ,p_tehl_tbl => p_tehl_tbl
1442 ,x_teh_rec => x_teh_rec
1443 ,x_tehl_tbl => x_tehl_tbl
1444 );
1445
1446 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1447 RAISE G_EXCEPTION_HALT_VALIDATION;
1448 ELSE
1449 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1450 l_return_status := x_return_status;
1451 END IF;
1452 END IF;
1453 -- Custom code if any
1454
1455 EXCEPTION
1456 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1457 NULL;
1458
1459 WHEN OTHERS THEN
1460 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1461 ,p_msg_name => g_unexpected_error
1462 ,p_token1 => g_sqlcode_token
1463 ,p_token1_value => sqlcode
1464 ,p_token2 => g_sqlerrm_token
1465 ,p_token2_value => sqlerrm);
1466 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1467 END create_trx_extension;
1468
1469 ----------------------------------------------------------------------------------
1470 -- Start of comments
1471 --
1472 -- Added : PRASJAIN : Bug# 6268782
1473 --
1474 -- Procedure Name : create_txl_extension
1475 -- Description : wrapper api for creating Transaction Extension
1476 -- Business Rules :
1477 -- Parameters : p_api_version ,p_init_msg_list,x_return_status
1478 -- ,x_msg_count ,x_msg_data ,p_tel_rec, p_tell_tbl
1479 -- ,x_tel_rec, x_tell_tbl
1480 -- Version : 1.0
1481 -- End of comments
1482 ----------------------------------------------------------------------------------
1483 PROCEDURE create_txl_extension(
1484 p_api_version IN NUMBER
1485 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1486 ,x_return_status OUT NOCOPY VARCHAR2
1487 ,x_msg_count OUT NOCOPY NUMBER
1488 ,x_msg_data OUT NOCOPY VARCHAR2
1489 ,p_tel_rec IN tel_rec_type
1490 ,p_tell_tbl IN tell_tbl_type
1491 ,x_tel_rec OUT NOCOPY tel_rec_type
1492 ,x_tell_tbl OUT NOCOPY tell_tbl_type) IS
1493 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1494 BEGIN
1495 okl_tel_pvt.insert_row(
1496 p_api_version => p_api_version
1497 ,p_init_msg_list => p_init_msg_list
1498 ,x_return_status => x_return_status
1499 ,x_msg_count => x_msg_count
1500 ,x_msg_data => x_msg_data
1501 ,p_tel_rec => p_tel_rec
1502 ,p_tell_tbl => p_tell_tbl
1503 ,x_tel_rec => x_tel_rec
1504 ,x_tell_tbl => x_tell_tbl
1505 );
1506 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1507 RAISE G_EXCEPTION_HALT_VALIDATION;
1508 ELSE
1509 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1510 l_return_status := x_return_status;
1511 END IF;
1512 END IF;
1513 EXCEPTION
1514 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1515 NULL;
1516 WHEN OTHERS THEN
1517 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1518 ,p_msg_name => g_unexpected_error
1519 ,p_token1 => g_sqlcode_token
1520 ,p_token1_value => sqlcode
1521 ,p_token2 => g_sqlerrm_token
1522 ,p_token2_value => sqlerrm);
1523 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1524 END create_txl_extension;
1525
1526 ----------------------------------------------------------------------------------
1527 -- Start of comments
1528 --
1529 -- Added : PRASJAIN : Bug# 6268782
1530 --
1531 -- Procedure Name : create_txl_extension
1532 -- Description : wrapper api for creating Transaction Extension
1533 -- Business Rules :
1534 -- Parameters : p_api_version ,p_init_msg_list,x_return_status
1535 -- ,x_msg_count ,x_msg_data ,p_tel_rec, p_tell_tbl
1536 -- ,x_tel_rec, x_tell_tbl
1537 -- Version : 1.0
1538 -- End of comments
1539 ----------------------------------------------------------------------------------
1540 PROCEDURE create_txl_extension(
1541 p_api_version IN NUMBER
1542 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1543 ,x_return_status OUT NOCOPY VARCHAR2
1544 ,x_msg_count OUT NOCOPY NUMBER
1545 ,x_msg_data OUT NOCOPY VARCHAR2
1546 ,p_tel_tbl_tbl IN tel_tbl_tbl_type
1547 ,x_tel_tbl_tbl OUT NOCOPY tel_tbl_tbl_type) IS
1548 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1549 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1550 i NUMBER := 0;
1551 BEGIN
1552
1553 OKC_API.init_msg_list(p_init_msg_list);
1554 -- Make sure PL/SQL table has records in it before passing
1555 IF (p_tel_tbl_tbl.COUNT > 0) THEN
1556 i := p_tel_tbl_tbl.FIRST;
1557 LOOP
1558 create_txl_extension(
1559 p_api_version => p_api_version
1560 ,p_init_msg_list => OKC_API.G_FALSE
1561 ,x_return_status => x_return_status
1562 ,x_msg_count => x_msg_count
1563 ,x_msg_data => x_msg_data
1564 ,p_tel_rec => p_tel_tbl_tbl(i).tel_rec
1565 ,p_tell_tbl => p_tel_tbl_tbl(i).tell_tbl
1566 ,x_tel_rec => x_tel_tbl_tbl(i).tel_rec
1567 ,x_tell_tbl => x_tel_tbl_tbl(i).tell_tbl);
1568
1569 -- store the highest degree of error
1570 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1571 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1572 l_overall_status := x_return_status;
1573 END IF;
1574 END IF;
1575
1576 EXIT WHEN (i = p_tel_tbl_tbl.LAST);
1577 i := p_tel_tbl_tbl.NEXT(i);
1578 END LOOP;
1579
1580 END IF;
1581
1582 x_return_status := l_overall_status;
1583
1584 EXCEPTION
1585 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1586 NULL;
1587 WHEN OTHERS THEN
1588 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1589 ,p_msg_name => g_unexpected_error
1590 ,p_token1 => g_sqlcode_token
1591 ,p_token1_value => sqlcode
1592 ,p_token2 => g_sqlerrm_token
1593 ,p_token2_value => sqlerrm);
1594 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1595 END create_txl_extension;
1596 END OKL_TRX_EXTENSION_PVT;