[Home] [Help]
PACKAGE BODY: APPS.OKL_TRX_CONTRACTS_PVT
Source
1 PACKAGE BODY OKL_TRX_CONTRACTS_PVT AS
2 /* $Header: OKLCTCNB.pls 120.10.12010000.3 2008/08/25 21:22:05 smereddy ship $ */
3
4 G_PRIMARY CONSTANT VARCHAR2(200) := 'PRIMARY';
5 G_SECONDARY CONSTANT VARCHAR2(200) := 'SECONDARY';
6
7 PROCEDURE create_trx_contracts(
8 p_api_version IN NUMBER
9 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
10 ,x_return_status OUT NOCOPY VARCHAR2
11 ,x_msg_count OUT NOCOPY NUMBER
12 ,x_msg_data OUT NOCOPY VARCHAR2
13 ,p_tcnv_rec IN tcnv_rec_type
14 ,p_tclv_tbl IN tclv_tbl_type
15 ,x_tcnv_rec OUT NOCOPY tcnv_rec_type
16 ,x_tclv_tbl OUT NOCOPY tclv_tbl_type
17 ) IS
18
19 i NUMBER;
20 l_tcnv_rec tcnv_rec_type;
21 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
22 l_tclv_tbl tclv_tbl_type := p_tclv_tbl;
23
24 BEGIN
25 -- Populate TRX CONTRACTS
26 create_trx_contracts(
27 p_api_version => p_api_version
28 ,p_init_msg_list => p_init_msg_list
29 ,x_return_status => x_return_status
30 ,x_msg_count => x_msg_count
31 ,x_msg_data => x_msg_data
32 ,p_tcnv_rec => p_tcnv_rec
33 ,x_tcnv_rec => x_tcnv_rec);
34
35 IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
36
37 -- populate the foreign key for the detail
38 IF (l_tclv_tbl.COUNT > 0) THEN
39 i:= l_tclv_tbl.FIRST;
40 LOOP
41 l_tclv_tbl(i).tcn_id := x_tcnv_rec.id;
42
43 -- Added by Santonyr on 25-Nov-2002
44 -- Get the currency from transaction if the passed one is null
45
46 IF (l_tclv_tbl(i).currency_code IS NULL) OR
47 (l_tclv_tbl(i).currency_code = OKL_API.G_MISS_CHAR) THEN
48 l_tclv_tbl(i).currency_code := x_tcnv_rec.currency_code;
49 END IF;
50
51 EXIT WHEN(i = l_tclv_tbl.LAST);
52 i := l_tclv_tbl.NEXT(i);
53 END LOOP;
54 END IF;
55
56
57 -- populate the detail
58 create_trx_cntrct_lines(
59 p_api_version => p_api_version
60 ,p_init_msg_list => p_init_msg_list
61 ,x_return_status => x_return_status
62 ,x_msg_count => x_msg_count
63 ,x_msg_data => x_msg_data
64 ,p_tclv_tbl => l_tclv_tbl
65 ,x_tclv_tbl => x_tclv_tbl);
66 END IF;
67
68 EXCEPTION
69 WHEN G_EXCEPTION_HALT_VALIDATION THEN
70 NULL;
71
72 WHEN OTHERS THEN
73 OKC_API.SET_MESSAGE(p_app_name => g_app_name
74 ,p_msg_name => g_unexpected_error
75 ,p_token1 => g_sqlcode_token
76 ,p_token1_value => sqlcode
77 ,p_token2 => g_sqlerrm_token
78 ,p_token2_value => sqlerrm);
79 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
80 END create_trx_contracts;
81
82 PROCEDURE create_trx_contracts(
83 p_api_version IN NUMBER
84 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
85 ,x_return_status OUT NOCOPY VARCHAR2
86 ,x_msg_count OUT NOCOPY NUMBER
87 ,x_msg_data OUT NOCOPY VARCHAR2
88 ,p_tcnv_rec IN tcnv_rec_type
89 ,x_tcnv_rec OUT NOCOPY tcnv_rec_type) IS
90 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
91 -- Added by zrehman for SLA project (Bug 5707866) 15-Feb-2007 start
92 CURSOR cntr_prod_csr(pdt_id OKL_PRODUCTS.ID%TYPE) IS
93 SELECT name
94 FROM okl_products
95 WHERE id = pdt_id;
96
97 CURSOR book_csr(p_id OKL_PRODUCTS.ID%TYPE) IS
98 SELECT quality_val, quality_name
99 FROM okl_prod_qlty_val_uv
100 WHERE pdt_id = p_id
101 AND QUALITY_NAME IN ('LEASE', 'INVESTOR');
102
103 CURSOR tax_csr(p_id OKL_PRODUCTS.ID%TYPE) IS
104 SELECT quality_val
105 FROM okl_prod_qlty_val_uv
106 WHERE pdt_id = p_id
107 AND QUALITY_NAME = 'TAXOWNER';
108
109 CURSOR representation_csr(set_of_books_id OKL_TRX_CONTRACTS.set_of_books_id%TYPE) IS
110 SELECT name,
111 short_name
112 FROM gl_ledgers
113 WHERE ledger_id = set_of_books_id;
114
115 CURSOR cntr_pid_csr(p_khr_id okl_k_headers.KHR_ID%TYPE) IS
116 SELECT pdt_id
117 FROM okl_k_headers
118 WHERE id = p_khr_id;
119
120 l_tcnv_rec tcnv_rec_type;
121 l_data_not_found NUMBER := 1;
122 l_quality_name OKL_PDT_QUALITYS.NAME%TYPE;
123 BEGIN
124 l_tcnv_rec := p_tcnv_rec;
125
126 --Modified by kthiruva for SLA Uptake
127 --Set the sob id only if it has not been set already.
128 IF(l_tcnv_rec.set_of_books_id IS NULL OR l_tcnv_rec.set_of_books_id = Okc_Api.G_MISS_NUM ) THEN
129 l_tcnv_rec.set_of_books_id := okl_accounting_util.get_set_of_books_id;
130 END IF;
131
132 IF(l_tcnv_rec.REPRESENTATION_TYPE IS NULL OR l_tcnv_rec.REPRESENTATION_TYPE = Okc_Api.G_MISS_CHAR ) THEN
133 l_tcnv_rec.REPRESENTATION_TYPE := G_PRIMARY;
134 END IF;
135
136 IF(l_tcnv_rec.representation_name IS NULL) OR (l_tcnv_rec.representation_name = Okc_Api.G_MISS_CHAR)
137 OR (l_tcnv_rec.representation_code IS NULL) OR (l_tcnv_rec.representation_code = Okc_Api.G_MISS_CHAR) THEN
138 OPEN representation_csr(l_tcnv_rec.set_of_books_id);
139 FETCH representation_csr into l_tcnv_rec.representation_name, l_tcnv_rec.representation_code;
140 IF representation_csr%NOTFOUND THEN
141 OKL_API.set_message(
142 p_app_name => G_APP_NAME,
143 p_msg_name => 'OKL_INVALID_VALUE',
144 p_token1 => 'COL_NAME',
145 p_token1_value => 'set_of_books_id'
146 );
147 RAISE OKC_API.G_EXCEPTION_ERROR;
148 END IF;
149 CLOSE representation_csr;
150 END IF;
151
152 -- Added by zrehman for SLA project, to allow for null pdt_id (Bug 5707866) 14-Mar-2007 start
153 IF(p_tcnv_rec.pdt_id IS NULL OR p_tcnv_rec.pdt_id = Okc_Api.G_MISS_NUM ) THEN
154 IF p_tcnv_rec.khr_id IS NOT NULL AND p_tcnv_rec.khr_id <>Okc_Api.G_MISS_NUM THEN
155 OPEN cntr_pid_csr(p_tcnv_rec.khr_id);
156 FETCH cntr_pid_csr INTO l_tcnv_rec.pdt_id;
157 IF cntr_pid_csr%NOTFOUND THEN
158 l_data_not_found := 0;
159 END IF;
160 CLOSE cntr_pid_csr;
161 IF(l_data_not_found = 0) THEN
162 OKL_API.set_message(
163 p_app_name => G_APP_NAME,
164 p_msg_name => 'OKL_INVALID_VALUE',
165 p_token1 => 'COL_NAME',
166 p_token1_value => 'khr_id'
167 );
168 RAISE OKC_API.G_EXCEPTION_ERROR;
169 END IF;
170 END IF;
171 END IF;
172 -- Added by zrehman for SLA project, to allow for null pdt_id (Bug 5707866) 14-Mar-2007 end
173
174 IF (l_tcnv_rec.PRODUCT_NAME IS NULL OR l_tcnv_rec.PRODUCT_NAME = Okc_Api.G_MISS_CHAR) THEN
175 IF l_tcnv_rec.pdt_id IS NOT NULL AND l_tcnv_rec.pdt_id <>Okc_Api.G_MISS_NUM THEN
176 OPEN cntr_prod_csr(l_tcnv_rec.pdt_id);
177 FETCH cntr_prod_csr INTO l_tcnv_rec.PRODUCT_NAME;
178 IF cntr_prod_csr%NOTFOUND THEN
179 l_data_not_found := 0;
180 END IF;
181 CLOSE cntr_prod_csr;
182 END IF;
183 END IF;
184
185 IF (l_tcnv_rec.book_classification_code IS NULL OR l_tcnv_rec.book_classification_code = Okc_Api.G_MISS_CHAR) THEN
186 IF l_tcnv_rec.pdt_id IS NOT NULL AND l_tcnv_rec.pdt_id <>Okc_Api.G_MISS_NUM THEN
187 OPEN book_csr(l_tcnv_rec.pdt_id);
188 FETCH book_csr INTO l_tcnv_rec.book_classification_code, l_quality_name;
189 IF book_csr%NOTFOUND THEN
190 l_data_not_found := 0;
191 END IF;
192 CLOSE book_csr;
193 END IF;
194 END IF;
195
196 IF l_quality_name = 'LEASE' THEN
197 IF (l_tcnv_rec.tax_owner_code IS NULL OR l_tcnv_rec.tax_owner_code = Okc_Api.G_MISS_CHAR) THEN
198 IF l_tcnv_rec.pdt_id IS NOT NULL AND l_tcnv_rec.pdt_id <>Okc_Api.G_MISS_NUM THEN
199 OPEN tax_csr(l_tcnv_rec.pdt_id);
200 FETCH tax_csr INTO l_tcnv_rec.tax_owner_code;
201 IF tax_csr%NOTFOUND THEN
202 l_data_not_found := 0;
203 END IF;
204 CLOSE tax_csr;
205 END IF;
206 END IF;
207 END IF;
208
209
210 IF(l_data_not_found = 0) THEN
211 OKL_API.set_message(
212 p_app_name => G_APP_NAME,
213 p_msg_name => 'OKL_INVALID_VALUE',
214 p_token1 => 'COL_NAME',
215 p_token1_value => 'pdt_id'
216 );
217 RAISE OKC_API.G_EXCEPTION_ERROR;
218 END IF;
219 -- Added by zrehman for SLA project (Bug 5707866) 15-Feb-2007 end
220 okl_tcn_pvt.insert_row(
221 p_api_version => p_api_version
222 ,p_init_msg_list => p_init_msg_list
223 ,x_return_status => x_return_status
224 ,x_msg_count => x_msg_count
225 ,x_msg_data => x_msg_data
226 ,p_tcnv_rec => l_tcnv_rec
227 ,x_tcnv_rec => x_tcnv_rec
228 );
229
230 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
231 RAISE G_EXCEPTION_HALT_VALIDATION;
232 ELSE
233 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
234 l_return_status := x_return_status;
235 END IF;
236 END IF;
237 -- Custom code if any
238
239 EXCEPTION
240 WHEN G_EXCEPTION_HALT_VALIDATION THEN
241 x_return_status := OKL_Api.G_RET_STS_UNEXP_ERROR;
242 WHEN OKL_API.G_EXCEPTION_ERROR THEN
243 x_return_status := OKL_API.G_RET_STS_ERROR;
244 WHEN OTHERS THEN
245 OKL_API.SET_MESSAGE(p_app_name => g_app_name
246 ,p_msg_name => g_unexpected_error
247 ,p_token1 => g_sqlcode_token
248 ,p_token1_value => sqlcode
249 ,p_token2 => g_sqlerrm_token
250 ,p_token2_value => sqlerrm);
251 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
252 END create_trx_contracts;
253
254 PROCEDURE create_trx_contracts(
255 p_api_version IN NUMBER
256 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
257 ,x_return_status OUT NOCOPY VARCHAR2
258 ,x_msg_count OUT NOCOPY NUMBER
259 ,x_msg_data OUT NOCOPY VARCHAR2
260 ,p_tcnv_tbl IN tcnv_tbl_type
261 ,x_tcnv_tbl OUT NOCOPY tcnv_tbl_type) IS
262 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
263 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
264 i NUMBER := 0;
265
266 BEGIN
267
268 Okc_Api.init_msg_list(p_init_msg_list);
269 -- Make sure PL/SQL table has records in it before passing
270 IF (p_tcnv_tbl.COUNT > 0) THEN
271 i := p_tcnv_tbl.FIRST;
272 LOOP
273 create_trx_contracts(
274 p_api_version => p_api_version,
275 p_init_msg_list => Okc_Api.G_FALSE,
276 x_return_status => x_return_status,
277 x_msg_count => x_msg_count,
278 x_msg_data => x_msg_data,
279 p_tcnv_rec => p_tcnv_tbl(i),
280 x_tcnv_rec => x_tcnv_tbl(i));
281 -- store the highest degree of error
282 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
283 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
284 l_overall_status := x_return_status;
285 END IF;
286 END IF;
287
288 EXIT WHEN (i = p_tcnv_tbl.LAST);
289 i := p_tcnv_tbl.NEXT(i);
290 END LOOP;
291
292 END IF;
293
294 x_return_status := l_overall_status;
295
296
297 EXCEPTION
298 WHEN G_EXCEPTION_HALT_VALIDATION THEN
299 NULL;
300
301 WHEN OTHERS THEN
302 OKC_API.SET_MESSAGE(p_app_name => g_app_name
303 ,p_msg_name => g_unexpected_error
304 ,p_token1 => g_sqlcode_token
305 ,p_token1_value => sqlcode
306 ,p_token2 => g_sqlerrm_token
307 ,p_token2_value => sqlerrm);
308 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
309
310 END create_trx_contracts;
311
312 -- Object type procedure for update
313 PROCEDURE update_trx_contracts(
314 p_api_version IN NUMBER,
315 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
316 x_return_status OUT NOCOPY VARCHAR2,
317 x_msg_count OUT NOCOPY NUMBER,
318 x_msg_data OUT NOCOPY VARCHAR2,
319 p_tcnv_rec IN tcnv_rec_type,
320 p_tclv_tbl IN tclv_tbl_type,
321 x_tcnv_rec OUT NOCOPY tcnv_rec_type,
322 x_tclv_tbl OUT NOCOPY tclv_tbl_type) IS
323 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
324 BEGIN
325 -- Update the master
326 update_trx_contracts(
327 p_api_version => p_api_version
328 ,p_init_msg_list => p_init_msg_list
329 ,x_return_status => x_return_status
330 ,x_msg_count => x_msg_count
331 ,x_msg_data => x_msg_data
332 ,p_tcnv_rec => p_tcnv_rec
333 ,x_tcnv_rec => x_tcnv_rec
334 );
335
336 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
337
338 -- Update the detail
339 update_trx_cntrct_lines(
340 p_api_version => p_api_version
341 ,p_init_msg_list => p_init_msg_list
342 ,x_return_status => x_return_status
343 ,x_msg_count => x_msg_count
344 ,x_msg_data => x_msg_data
345 ,p_tclv_tbl => p_tclv_tbl
346 ,x_tclv_tbl => x_tclv_tbl
347 );
348
349 END IF;
350
351
352 EXCEPTION
353 WHEN G_EXCEPTION_HALT_VALIDATION THEN
354 NULL;
355
356 WHEN OTHERS THEN
357 OKC_API.SET_MESSAGE(p_app_name => g_app_name
358 ,p_msg_name => g_unexpected_error
359 ,p_token1 => g_sqlcode_token
360 ,p_token1_value => sqlcode
361 ,p_token2 => g_sqlerrm_token
362 ,p_token2_value => sqlerrm);
363 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
364
365 END update_trx_contracts;
366
367 PROCEDURE validate_trx_contracts(
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_tcnv_rec IN tcnv_rec_type
374 ,p_tclv_tbl IN tclv_tbl_type) IS
375
376 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
377 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
378
379 BEGIN
380 -- Validate the master
381 validate_trx_contracts(
382 p_api_version => p_api_version
383 ,p_init_msg_list => p_init_msg_list
384 ,x_return_status => x_return_status
385 ,x_msg_count => x_msg_count
386 ,x_msg_data => x_msg_data
387 ,p_tcnv_rec => p_tcnv_rec
388 );
389
390 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
391 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
392 l_overall_status := x_return_status;
393 END IF;
394 END IF;
395
396 -- Validate the detail
397 validate_trx_cntrct_lines(
398 p_api_version => p_api_version
399 ,p_init_msg_list => p_init_msg_list
400 ,x_return_status => x_return_status
401 ,x_msg_count => x_msg_count
402 ,x_msg_data => x_msg_data
403 ,p_tclv_tbl => p_tclv_tbl
404 );
405
406 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
407 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
408 l_overall_status := x_return_status;
409 END IF;
410 END IF;
411
412 x_return_status := l_overall_status;
413
414 EXCEPTION
415 WHEN G_EXCEPTION_HALT_VALIDATION THEN
416 NULL;
417 WHEN OTHERS THEN
418 OKC_API.SET_MESSAGE(p_app_name => g_app_name
419 ,p_msg_name => g_unexpected_error
420 ,p_token1 => g_sqlcode_token
421 ,p_token1_value => sqlcode
422 ,p_token2 => g_sqlerrm_token
423 ,p_token2_value => sqlerrm);
424 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
425
426 END validate_trx_contracts;
427
428 PROCEDURE lock_trx_contracts(
429 p_api_version IN NUMBER
430 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
431 ,x_return_status OUT NOCOPY VARCHAR2
432 ,x_msg_count OUT NOCOPY NUMBER
433 ,x_msg_data OUT NOCOPY VARCHAR2
434 ,p_tcnv_rec IN tcnv_rec_type) IS
435 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
436
437 BEGIN
438 okl_tcn_pvt.lock_row(
439 p_api_version => p_api_version
440 ,p_init_msg_list => p_init_msg_list
441 ,x_return_status => x_return_status
442 ,x_msg_count => x_msg_count
443 ,x_msg_data => x_msg_data
444 ,p_tcnv_rec => p_tcnv_rec
445 );
446 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
447 RAISE G_EXCEPTION_HALT_VALIDATION;
448 ELSE
449 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
450 l_return_status := x_return_status;
451 END IF;
452 END IF;
453
454 EXCEPTION
455 WHEN G_EXCEPTION_HALT_VALIDATION THEN
456 NULL;
457 WHEN OTHERS THEN
458 OKC_API.SET_MESSAGE(p_app_name => g_app_name
459 ,p_msg_name => g_unexpected_error
460 ,p_token1 => g_sqlcode_token
461 ,p_token1_value => sqlcode
462 ,p_token2 => g_sqlerrm_token
463 ,p_token2_value => sqlerrm);
464 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
465 END lock_trx_contracts;
466
467 PROCEDURE lock_trx_contracts(
468 p_api_version IN NUMBER
469 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
470 ,x_return_status OUT NOCOPY VARCHAR2
471 ,x_msg_count OUT NOCOPY NUMBER
472 ,x_msg_data OUT NOCOPY VARCHAR2
473 ,p_tcnv_tbl IN tcnv_tbl_type) IS
474
475 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
476 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
477 i NUMBER := 0;
478
479 BEGIN
480
481 Okc_Api.init_msg_list(p_init_msg_list);
482 -- Make sure PL/SQL table has records in it before passing
483 IF (p_tcnv_tbl.COUNT > 0) THEN
484 i := p_tcnv_tbl.FIRST;
485 LOOP
486 lock_trx_contracts(
487 p_api_version => p_api_version,
488 p_init_msg_list => Okc_Api.G_FALSE,
489 x_return_status => x_return_status,
490 x_msg_count => x_msg_count,
491 x_msg_data => x_msg_data,
492 p_tcnv_rec => p_tcnv_tbl(i));
493
494 -- store the highest degree of error
495 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
496 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
497 l_overall_status := x_return_status;
498 END IF;
499 END IF;
500
501 EXIT WHEN (i = p_tcnv_tbl.LAST);
502 i := p_tcnv_tbl.NEXT(i);
503 END LOOP;
504
505 END IF;
506
507 x_return_status := l_overall_status;
508
509 EXCEPTION
510 WHEN G_EXCEPTION_HALT_VALIDATION THEN
511 NULL;
512 WHEN OTHERS THEN
513 OKC_API.SET_MESSAGE(p_app_name => g_app_name
514 ,p_msg_name => g_unexpected_error
515 ,p_token1 => g_sqlcode_token
516 ,p_token1_value => sqlcode
517 ,p_token2 => g_sqlerrm_token
518 ,p_token2_value => sqlerrm);
519 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
520 END lock_trx_contracts;
521
522 PROCEDURE update_trx_contracts(
523 p_api_version IN NUMBER
524 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
525 ,x_return_status OUT NOCOPY VARCHAR2
526 ,x_msg_count OUT NOCOPY NUMBER
527 ,x_msg_data OUT NOCOPY VARCHAR2
528 ,p_tcnv_rec IN tcnv_rec_type
529 ,x_tcnv_rec OUT NOCOPY tcnv_rec_type) IS
530 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
531 l_tcnv_rec tcnv_rec_type;
532 BEGIN
533 -- Added by zrehman on 16-Mar-2007 as part of SLA Bug#5707866 Start
534 l_tcnv_rec := p_tcnv_rec;
535 IF l_tcnv_rec.TSU_CODE = 'CANCELED' THEN
536 l_tcnv_rec.ACCOUNTING_REVERSAL_YN := 'Y';
537 END IF;
538 -- Added by zrehman on 16-Mar-2007 as part of SLA Bug#5707866 End
539 okl_tcn_pvt.update_row(
540 p_api_version => p_api_version
541 ,p_init_msg_list => p_init_msg_list
542 ,x_return_status => x_return_status
543 ,x_msg_count => x_msg_count
544 ,x_msg_data => x_msg_data
545 ,p_tcnv_rec => l_tcnv_rec -- Bug#5707866
546 ,x_tcnv_rec => x_tcnv_rec
547 );
548 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
549 RAISE G_EXCEPTION_HALT_VALIDATION;
550 ELSE
551 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
552 l_return_status := x_return_status;
553 END IF;
554 END IF;
555 EXCEPTION
556 WHEN G_EXCEPTION_HALT_VALIDATION THEN
557 NULL;
558 WHEN OTHERS THEN
559 OKC_API.SET_MESSAGE(p_app_name => g_app_name
560 ,p_msg_name => g_unexpected_error
561 ,p_token1 => g_sqlcode_token
562 ,p_token1_value => sqlcode
563 ,p_token2 => g_sqlerrm_token
564 ,p_token2_value => sqlerrm);
565 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
566 END update_trx_contracts;
567
568 PROCEDURE update_trx_contracts(
569 p_api_version IN NUMBER
570 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
571 ,x_return_status OUT NOCOPY VARCHAR2
572 ,x_msg_count OUT NOCOPY NUMBER
573 ,x_msg_data OUT NOCOPY VARCHAR2
574 ,p_tcnv_tbl IN tcnv_tbl_type
575 ,x_tcnv_tbl OUT NOCOPY tcnv_tbl_type) IS
576 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
577 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
578 i NUMBER := 0;
579
580 BEGIN
581
582 Okc_Api.init_msg_list(p_init_msg_list);
583 -- Make sure PL/SQL table has records in it before passing
584 IF (p_tcnv_tbl.COUNT > 0) THEN
585 i := p_tcnv_tbl.FIRST;
586 LOOP
587 update_trx_contracts(
588 p_api_version => p_api_version,
589 p_init_msg_list => Okc_Api.G_FALSE,
590 x_return_status => x_return_status,
591 x_msg_count => x_msg_count,
592 x_msg_data => x_msg_data,
593 p_tcnv_rec => p_tcnv_tbl(i),
594 x_tcnv_rec => x_tcnv_tbl(i));
595
596 -- store the highest degree of error
597 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
598 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
599 l_overall_status := x_return_status;
600 END IF;
601 END IF;
602
603 EXIT WHEN (i = p_tcnv_tbl.LAST);
604 i := p_tcnv_tbl.NEXT(i);
605 END LOOP;
606
607 END IF;
608
609 x_return_status := l_overall_status;
610
611
612 EXCEPTION
613 WHEN G_EXCEPTION_HALT_VALIDATION THEN
614 NULL;
615 WHEN OTHERS THEN
616 OKC_API.SET_MESSAGE(p_app_name => g_app_name
617 ,p_msg_name => g_unexpected_error
618 ,p_token1 => g_sqlcode_token
619 ,p_token1_value => sqlcode
620 ,p_token2 => g_sqlerrm_token
621 ,p_token2_value => sqlerrm);
622 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
623 END update_trx_contracts;
624
625 --Put custom code for cascade delete by developer
626 PROCEDURE delete_trx_contracts(
627 p_api_version IN NUMBER
628 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
629 ,x_return_status OUT NOCOPY VARCHAR2
630 ,x_msg_count OUT NOCOPY NUMBER
631 ,x_msg_data OUT NOCOPY VARCHAR2
632 ,p_tcnv_rec IN tcnv_rec_type) IS
633 i NUMBER :=0;
634 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
635 l_tclv_tbl tclv_tbl_type;
636
637 CURSOR tcl_csr IS
638 SELECT tcl.id
639 FROM OKL_TXL_CNTRCT_LNS tcl
640 WHERE tcl.tcn_id = p_tcnv_rec.id;
641 BEGIN
642 FOR tcl_rec IN tcl_csr
643 LOOP
644 i := i + 1;
645 l_tclv_tbl(i).id := tcl_rec.id;
646 END LOOP;
647
648 delete_trx_cntrct_lines( p_api_version => p_api_version
649 ,p_init_msg_list => p_init_msg_list
650 ,x_return_status => x_return_status
651 ,x_msg_count => x_msg_count
652 ,x_msg_data => x_msg_data
653 ,p_tclv_tbl => l_tclv_tbl);
654
655
656 IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
657 okl_tcn_pvt.delete_row(p_api_version => p_api_version
658 ,p_init_msg_list => p_init_msg_list
659 ,x_return_status => x_return_status
660 ,x_msg_count => x_msg_count
661 ,x_msg_data => x_msg_data
662 ,p_tcnv_rec => p_tcnv_rec);
663
664 END IF;
665
666
667 EXCEPTION
668 WHEN G_EXCEPTION_HALT_VALIDATION THEN
669 NULL;
670 WHEN OTHERS THEN
671 OKC_API.SET_MESSAGE(p_app_name => g_app_name
672 ,p_msg_name => g_unexpected_error
673 ,p_token1 => g_sqlcode_token
674 ,p_token1_value => sqlcode
675 ,p_token2 => g_sqlerrm_token
676 ,p_token2_value => sqlerrm);
677
678 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
679 END delete_trx_contracts;
680
681
682 PROCEDURE delete_trx_contracts(
683 p_api_version IN NUMBER
684 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
685 ,x_return_status OUT NOCOPY VARCHAR2
686 ,x_msg_count OUT NOCOPY NUMBER
687 ,x_msg_data OUT NOCOPY VARCHAR2
688 ,p_tcnv_tbl IN tcnv_tbl_type) IS
689 i NUMBER :=0;
690 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
691 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
692
693 BEGIN
694 --Initialize the return status
695 x_return_status := OKC_API.G_RET_STS_SUCCESS;
696 IF (p_tcnv_tbl.COUNT > 0) THEN
697 i := p_tcnv_tbl.FIRST;
698 LOOP
699 delete_trx_contracts(
700 p_api_version => p_api_version
701 ,p_init_msg_list => p_init_msg_list
702 ,x_return_status => x_return_status
703 ,x_msg_count => x_msg_count
704 ,x_msg_data => x_msg_data
705 ,p_tcnv_rec => p_tcnv_tbl(i)
706 );
707
708 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
709 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
710 l_overall_status := x_return_status;
711 END IF;
712 END IF;
713
714 EXIT WHEN (i = p_tcnv_tbl.LAST);
715 i := p_tcnv_tbl.NEXT(i);
716 END LOOP;
717
718 END IF;
719
720 x_return_status := l_overall_status;
721
722 EXCEPTION
723 WHEN G_EXCEPTION_HALT_VALIDATION THEN
724 NULL;
725 WHEN OTHERS THEN
726 OKC_API.SET_MESSAGE(p_app_name => g_app_name
727 ,p_msg_name => g_unexpected_error
728 ,p_token1 => g_sqlcode_token
729 ,p_token1_value => sqlcode
730 ,p_token2 => g_sqlerrm_token
731 ,p_token2_value => sqlerrm);
732 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
733 END delete_trx_contracts;
734
735 PROCEDURE validate_trx_contracts(
736 p_api_version IN NUMBER
737 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
738 ,x_return_status OUT NOCOPY VARCHAR2
739 ,x_msg_count OUT NOCOPY NUMBER
740 ,x_msg_data OUT NOCOPY VARCHAR2
741 ,p_tcnv_rec IN tcnv_rec_type) IS
742 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
743 BEGIN
744 okl_tcn_pvt.validate_row(
745 p_api_version => p_api_version
746 ,p_init_msg_list => p_init_msg_list
747 ,x_return_status => x_return_status
748 ,x_msg_count => x_msg_count
749 ,x_msg_data => x_msg_data
750 ,p_tcnv_rec => p_tcnv_rec
751 );
752 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
753 RAISE G_EXCEPTION_HALT_VALIDATION;
754 ELSE
755 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
756 l_return_status := x_return_status;
757 END IF;
758 END IF;
759 EXCEPTION
760 WHEN G_EXCEPTION_HALT_VALIDATION THEN
761 NULL;
762 WHEN OTHERS THEN
763 OKC_API.SET_MESSAGE(p_app_name => g_app_name
764 ,p_msg_name => g_unexpected_error
765 ,p_token1 => g_sqlcode_token
766 ,p_token1_value => sqlcode
767 ,p_token2 => g_sqlerrm_token
768 ,p_token2_value => sqlerrm);
769 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
770 END validate_trx_contracts;
771
772 PROCEDURE validate_trx_contracts(
773 p_api_version IN NUMBER,
774 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
775 x_return_status OUT NOCOPY VARCHAR2,
776 x_msg_count OUT NOCOPY NUMBER,
777 x_msg_data OUT NOCOPY VARCHAR2,
778 p_tcnv_tbl IN tcnv_tbl_type) IS
779 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
780 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
781 i NUMBER := 0;
782
783 BEGIN
784
785 Okc_Api.init_msg_list(p_init_msg_list);
786 -- Make sure PL/SQL table has records in it before passing
787 IF (p_tcnv_tbl.COUNT > 0) THEN
788 i := p_tcnv_tbl.FIRST;
789 LOOP
790 validate_trx_contracts(
791 p_api_version => p_api_version,
792 p_init_msg_list => Okc_Api.G_FALSE,
793 x_return_status => x_return_status,
794 x_msg_count => x_msg_count,
795 x_msg_data => x_msg_data,
796 p_tcnv_rec => p_tcnv_tbl(i));
797
798 -- store the highest degree of error
799 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
800 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
801 l_overall_status := x_return_status;
802 END IF;
803 END IF;
804
805 EXIT WHEN (i = p_tcnv_tbl.LAST);
806 i := p_tcnv_tbl.NEXT(i);
807 END LOOP;
808
809 END IF;
810
811 x_return_status := l_overall_status;
812
813
814 EXCEPTION
815 WHEN G_EXCEPTION_HALT_VALIDATION THEN
816 NULL;
817 WHEN OTHERS THEN
818 OKC_API.SET_MESSAGE(p_app_name => g_app_name
819 ,p_msg_name => g_unexpected_error
820 ,p_token1 => g_sqlcode_token
821 ,p_token1_value => sqlcode
822 ,p_token2 => g_sqlerrm_token
823 ,p_token2_value => sqlerrm);
824 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
825 END validate_trx_contracts;
826
827 PROCEDURE create_trx_cntrct_lines(
828 p_api_version IN NUMBER
829 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
830 ,x_return_status OUT NOCOPY VARCHAR2
831 ,x_msg_count OUT NOCOPY NUMBER
832 ,x_msg_data OUT NOCOPY VARCHAR2
833 ,p_tclv_rec IN tclv_rec_type
834 ,x_tclv_rec OUT NOCOPY tclv_rec_type) IS
835 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
836 l_stream_type_code OKL_TXL_CNTRCT_LNS.STREAM_TYPE_CODE%TYPE;
837 l_stream_type_purpose OKL_TXL_CNTRCT_LNS.STREAM_TYPE_PURPOSE%TYPE;
838 CURSOR okl_strm_type_csr(sty_id IN OKL_TXL_CNTRCT_LNS.STY_ID%TYPE) IS
839 SELECT
840 code,
841 stream_type_purpose
842 FROM
843 OKL_STRM_TYPE_B
844 WHERE
845 id = sty_id;
846 l_tclv_rec tclv_rec_type;
847 BEGIN
848 -- Added by zrehman for SLA project (Bug 5707866) 8-Feb-2007 start
849 l_tclv_rec := p_tclv_rec;
850 IF (p_tclv_rec.sty_id IS NOT NULL AND p_tclv_rec.sty_id <> OKC_API.G_MISS_NUM) THEN
851 OPEN okl_strm_type_csr(p_tclv_rec.sty_id);
852 FETCH okl_strm_type_csr INTO
853 l_stream_type_code,
854 l_stream_type_purpose;
855 IF okl_strm_type_csr%NOTFOUND THEN
856 OKL_API.set_message(
857 p_app_name => G_APP_NAME,
858 p_msg_name => 'OKL_INVALID_VALUE',
859 p_token1 => 'COL_NAME',
860 p_token1_value => 'sty_id'
861 );
862 RAISE OKC_API.G_EXCEPTION_ERROR;
863 END IF;
864 CLOSE okl_strm_type_csr;
865 l_tclv_rec.stream_type_purpose := l_stream_type_purpose;
866 l_tclv_rec.stream_type_code := l_stream_type_code;
867 END IF;
868 -- Added by zrehman for SLA project (Bug 5707866) 8-Feb-2007 end
869 okl_tcl_pvt.insert_row(
870 p_api_version => p_api_version
871 ,p_init_msg_list => p_init_msg_list
872 ,x_return_status => x_return_status
873 ,x_msg_count => x_msg_count
874 ,x_msg_data => x_msg_data
875 ,p_tclv_rec => l_tclv_rec
876 ,x_tclv_rec => x_tclv_rec
877 );
878 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
879 RAISE G_EXCEPTION_HALT_VALIDATION;
880 ELSE
881 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
882 l_return_status := x_return_status;
883 END IF;
884 END IF;
885 EXCEPTION
886 WHEN G_EXCEPTION_HALT_VALIDATION THEN
887 NULL;
888 WHEN OTHERS THEN
889 OKC_API.SET_MESSAGE(p_app_name => g_app_name
890 ,p_msg_name => g_unexpected_error
891 ,p_token1 => g_sqlcode_token
892 ,p_token1_value => sqlcode
893 ,p_token2 => g_sqlerrm_token
894 ,p_token2_value => sqlerrm);
895 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
896 END create_trx_cntrct_lines;
897
898 PROCEDURE create_trx_cntrct_lines(
899 p_api_version IN NUMBER
900 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
901 ,x_return_status OUT NOCOPY VARCHAR2
902 ,x_msg_count OUT NOCOPY NUMBER
903 ,x_msg_data OUT NOCOPY VARCHAR2
904 ,p_tclv_tbl IN tclv_tbl_type
905 ,x_tclv_tbl OUT NOCOPY tclv_tbl_type) IS
906 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
907 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
908 i NUMBER := 0;
909
910 BEGIN
911
912 OKC_API.init_msg_list(p_init_msg_list);
913 -- Make sure PL/SQL table has records in it before passing
914 IF (p_tclv_tbl.COUNT > 0) THEN
915 i := p_tclv_tbl.FIRST;
916 LOOP
917 create_trx_cntrct_lines(
918 p_api_version => p_api_version,
919 p_init_msg_list => OKC_API.G_FALSE,
920 x_return_status => x_return_status,
921 x_msg_count => x_msg_count,
922 x_msg_data => x_msg_data,
923 p_tclv_rec => p_tclv_tbl(i),
924 x_tclv_rec => x_tclv_tbl(i));
925
926 -- store the highest degree of error
927 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
928 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
929 l_overall_status := x_return_status;
930 END IF;
931 END IF;
932
933 EXIT WHEN (i = p_tclv_tbl.LAST);
934 i := p_tclv_tbl.NEXT(i);
935 END LOOP;
936
937 END IF;
938
939 x_return_status := l_overall_status;
940
941 EXCEPTION
942 WHEN G_EXCEPTION_HALT_VALIDATION THEN
943 NULL;
944 WHEN OTHERS THEN
945 OKC_API.SET_MESSAGE(p_app_name => g_app_name
946 ,p_msg_name => g_unexpected_error
947 ,p_token1 => g_sqlcode_token
948 ,p_token1_value => sqlcode
949 ,p_token2 => g_sqlerrm_token
950 ,p_token2_value => sqlerrm);
951 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
952 END create_trx_cntrct_lines;
953
954 PROCEDURE lock_trx_cntrct_lines(
955 p_api_version IN NUMBER
956 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
957 ,x_return_status OUT NOCOPY VARCHAR2
958 ,x_msg_count OUT NOCOPY NUMBER
959 ,x_msg_data OUT NOCOPY VARCHAR2
960 ,p_tclv_rec IN tclv_rec_type) IS
961 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
962 BEGIN
963 okl_tcl_pvt.lock_row(
964 p_api_version => p_api_version
965 ,p_init_msg_list => p_init_msg_list
966 ,x_return_status => x_return_status
967 ,x_msg_count => x_msg_count
968 ,x_msg_data => x_msg_data
969 ,p_tclv_rec => p_tclv_rec
970 );
971 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
972 RAISE G_EXCEPTION_HALT_VALIDATION;
973 ELSE
974 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
975 l_return_status := x_return_status;
976 END IF;
977 END IF;
978 EXCEPTION
979 WHEN G_EXCEPTION_HALT_VALIDATION THEN
980 NULL;
981 WHEN OTHERS THEN
982 OKC_API.SET_MESSAGE(p_app_name => g_app_name
983 ,p_msg_name => g_unexpected_error
984 ,p_token1 => g_sqlcode_token
985 ,p_token1_value => sqlcode
986 ,p_token2 => g_sqlerrm_token
987 ,p_token2_value => sqlerrm);
988 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
989
990 END lock_trx_cntrct_lines;
991
992 PROCEDURE lock_trx_cntrct_lines(
993 p_api_version IN NUMBER
994 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
995 ,x_return_status OUT NOCOPY VARCHAR2
996 ,x_msg_count OUT NOCOPY NUMBER
997 ,x_msg_data OUT NOCOPY VARCHAR2
998 ,p_tclv_tbl IN tclv_tbl_type) IS
999 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1000 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1001 i NUMBER := 0;
1002
1003 BEGIN
1004
1005 OKC_API.init_msg_list(p_init_msg_list);
1006 -- Make sure PL/SQL table has records in it before passing
1007 IF (p_tclv_tbl.COUNT > 0) THEN
1008 i := p_tclv_tbl.FIRST;
1009 LOOP
1010 lock_trx_cntrct_lines(
1011 p_api_version => p_api_version,
1012 p_init_msg_list => OKC_API.G_FALSE,
1013 x_return_status => x_return_status,
1014 x_msg_count => x_msg_count,
1015 x_msg_data => x_msg_data,
1016 p_tclv_rec => p_tclv_tbl(i));
1017
1018 -- store the highest degree of error
1019 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1020 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1021 l_overall_status := x_return_status;
1022 END IF;
1023 END IF;
1024
1025 EXIT WHEN (i = p_tclv_tbl.LAST);
1026 i := p_tclv_tbl.NEXT(i);
1027
1028 END LOOP;
1029
1030 END IF;
1031
1032 x_return_status := l_overall_status;
1033
1034
1035 EXCEPTION
1036 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1037 NULL;
1038 WHEN OTHERS THEN
1039 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1040 ,p_msg_name => g_unexpected_error
1041 ,p_token1 => g_sqlcode_token
1042 ,p_token1_value => sqlcode
1043 ,p_token2 => g_sqlerrm_token
1044 ,p_token2_value => sqlerrm);
1045 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1046 END lock_trx_cntrct_lines;
1047
1048 PROCEDURE update_trx_cntrct_lines(
1049 p_api_version IN NUMBER
1050 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1051 ,x_return_status OUT NOCOPY VARCHAR2
1052 ,x_msg_count OUT NOCOPY NUMBER
1053 ,x_msg_data OUT NOCOPY VARCHAR2
1054 ,p_tclv_rec IN tclv_rec_type
1055 ,x_tclv_rec OUT NOCOPY tclv_rec_type) IS
1056 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1057
1058 BEGIN
1059 okl_tcl_pvt.update_row(
1060 p_api_version => p_api_version
1061 ,p_init_msg_list => p_init_msg_list
1062 ,x_return_status => x_return_status
1063 ,x_msg_count => x_msg_count
1064 ,x_msg_data => x_msg_data
1065 ,p_tclv_rec => p_tclv_rec
1066 ,x_tclv_rec => x_tclv_rec
1067 );
1068 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1069 RAISE G_EXCEPTION_HALT_VALIDATION;
1070 ELSE
1071 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1072 l_return_status := x_return_status;
1073 END IF;
1074 END IF;
1075 EXCEPTION
1076 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1077 NULL;
1078 WHEN OTHERS THEN
1079 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1080 ,p_msg_name => g_unexpected_error
1081 ,p_token1 => g_sqlcode_token
1082 ,p_token1_value => sqlcode
1083 ,p_token2 => g_sqlerrm_token
1084 ,p_token2_value => sqlerrm);
1085 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1086 END update_trx_cntrct_lines;
1087
1088 PROCEDURE update_trx_cntrct_lines(
1089 p_api_version IN NUMBER
1090 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1091 ,x_return_status OUT NOCOPY VARCHAR2
1092 ,x_msg_count OUT NOCOPY NUMBER
1093 ,x_msg_data OUT NOCOPY VARCHAR2
1094 ,p_tclv_tbl IN tclv_tbl_type
1095 ,x_tclv_tbl OUT NOCOPY tclv_tbl_type) IS
1096
1097 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1098 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1099 i NUMBER := 0;
1100
1101 BEGIN
1102
1103 OKC_API.init_msg_list(p_init_msg_list);
1104 -- Make sure PL/SQL table has records in it before passing
1105 IF (p_tclv_tbl.COUNT > 0) THEN
1106 i := p_tclv_tbl.FIRST;
1107 LOOP
1108 update_trx_cntrct_lines(
1109 p_api_version => p_api_version,
1110 p_init_msg_list => OKC_API.G_FALSE,
1111 x_return_status => x_return_status,
1112 x_msg_count => x_msg_count,
1113 x_msg_data => x_msg_data,
1114 p_tclv_rec => p_tclv_tbl(i),
1115 x_tclv_rec => x_tclv_tbl(i));
1116
1117 -- store the highest degree of error
1118 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1119 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1120 l_overall_status := x_return_status;
1121 END IF;
1122 END IF;
1123
1124 EXIT WHEN (i = p_tclv_tbl.LAST);
1125 i := p_tclv_tbl.NEXT(i);
1126 END LOOP;
1127
1128 END IF;
1129
1130 x_return_status := l_overall_status;
1131
1132
1133 EXCEPTION
1134 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1135 NULL;
1136 WHEN OTHERS THEN
1137 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1138 ,p_msg_name => g_unexpected_error
1139 ,p_token1 => g_sqlcode_token
1140 ,p_token1_value => sqlcode
1141 ,p_token2 => g_sqlerrm_token
1142 ,p_token2_value => sqlerrm);
1143 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1144 END update_trx_cntrct_lines;
1145
1146 --Put custom code for cascade delete by developer
1147 PROCEDURE delete_trx_cntrct_lines(
1148 p_api_version IN NUMBER
1149 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1150 ,x_return_status OUT NOCOPY VARCHAR2
1151 ,x_msg_count OUT NOCOPY NUMBER
1152 ,x_msg_data OUT NOCOPY VARCHAR2
1153 ,p_tclv_rec IN tclv_rec_type) IS
1154 i NUMBER :=0;
1155 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1156 BEGIN
1157 okl_tcl_pvt.delete_row(
1158 p_api_version => p_api_version
1159 ,p_init_msg_list => p_init_msg_list
1160 ,x_return_status => x_return_status
1161 ,x_msg_count => x_msg_count
1162 ,x_msg_data => x_msg_data
1163 ,p_tclv_rec => p_tclv_rec);
1164 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1165 RAISE G_EXCEPTION_HALT_VALIDATION;
1166 ELSE
1167 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1168 l_return_status := x_return_status;
1169 END IF;
1170 END IF;
1171 EXCEPTION
1172 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1173 NULL;
1174 WHEN OTHERS THEN
1175 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1176 ,p_msg_name => g_unexpected_error
1177 ,p_token1 => g_sqlcode_token
1178 ,p_token1_value => sqlcode
1179 ,p_token2 => g_sqlerrm_token
1180 ,p_token2_value => sqlerrm);
1181 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1182 END delete_trx_cntrct_lines;
1183
1184 PROCEDURE delete_trx_cntrct_lines(
1185 p_api_version IN NUMBER
1186 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1187 ,x_return_status OUT NOCOPY VARCHAR2
1188 ,x_msg_count OUT NOCOPY NUMBER
1189 ,x_msg_data OUT NOCOPY VARCHAR2
1190 ,p_tclv_tbl IN tclv_tbl_type) IS
1191
1192 i NUMBER :=0;
1193 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1194 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1195
1196 BEGIN
1197 --Initialize the return status
1198 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1199 IF (p_tclv_tbl.COUNT > 0) THEN
1200 i := p_tclv_tbl.FIRST;
1201 LOOP
1202 delete_trx_cntrct_lines(
1203 p_api_version => p_api_version
1204 ,p_init_msg_list => p_init_msg_list
1205 ,x_return_status => x_return_status
1206 ,x_msg_count => x_msg_count
1207 ,x_msg_data => x_msg_data
1208 ,p_tclv_rec => p_tclv_tbl(i));
1209
1210 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1211 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1212 l_overall_status := x_return_status;
1213 END IF;
1214 END IF;
1215
1216 EXIT WHEN (i = p_tclv_tbl.LAST);
1217 i := p_tclv_tbl.NEXT(i);
1218 END LOOP;
1219
1220 END IF;
1221
1222 x_return_status := l_overall_status;
1223
1224 EXCEPTION
1225 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1226 NULL;
1227 WHEN OTHERS THEN
1228 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1229 ,p_msg_name => g_unexpected_error
1230 ,p_token1 => g_sqlcode_token
1231 ,p_token1_value => sqlcode
1232 ,p_token2 => g_sqlerrm_token
1233 ,p_token2_value => sqlerrm);
1234 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1235 END delete_trx_cntrct_lines;
1236
1237 PROCEDURE validate_trx_cntrct_lines(
1238 p_api_version IN NUMBER
1239 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1240 ,x_return_status OUT NOCOPY VARCHAR2
1241 ,x_msg_count OUT NOCOPY NUMBER
1242 ,x_msg_data OUT NOCOPY VARCHAR2
1243 ,p_tclv_rec IN tclv_rec_type) IS
1244 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1245 BEGIN
1246 okl_tcl_pvt.validate_row(
1247 p_api_version => p_api_version
1248 ,p_init_msg_list => p_init_msg_list
1249 ,x_return_status => x_return_status
1250 ,x_msg_count => x_msg_count
1251 ,x_msg_data => x_msg_data
1252 ,p_tclv_rec => p_tclv_rec
1253 );
1254 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1255 RAISE G_EXCEPTION_HALT_VALIDATION;
1256 ELSE
1257 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1258 l_return_status := x_return_status;
1259 END IF;
1260 END IF;
1261 EXCEPTION
1262 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1263 NULL;
1264 WHEN OTHERS THEN
1265 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1266 ,p_msg_name => g_unexpected_error
1267 ,p_token1 => g_sqlcode_token
1268 ,p_token1_value => sqlcode
1269 ,p_token2 => g_sqlerrm_token
1270 ,p_token2_value => sqlerrm);
1271 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1272 END validate_trx_cntrct_lines;
1273
1274 PROCEDURE validate_trx_cntrct_lines(
1275 p_api_version IN NUMBER
1276 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1277 ,x_return_status OUT NOCOPY VARCHAR2
1278 ,x_msg_count OUT NOCOPY NUMBER
1279 ,x_msg_data OUT NOCOPY VARCHAR2
1280 ,p_tclv_tbl IN tclv_tbl_type) IS
1281 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1282 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1283 i NUMBER := 0;
1284
1285 BEGIN
1286
1287 OKC_API.init_msg_list(p_init_msg_list);
1288 -- Make sure PL/SQL table has records in it before passing
1289 IF (p_tclv_tbl.COUNT > 0) THEN
1290 i := p_tclv_tbl.FIRST;
1291 LOOP
1292 validate_trx_cntrct_lines(
1293 p_api_version => p_api_version,
1294 p_init_msg_list => OKC_API.G_FALSE,
1295 x_return_status => x_return_status,
1296 x_msg_count => x_msg_count,
1297 x_msg_data => x_msg_data,
1298 p_tclv_rec => p_tclv_tbl(i));
1299
1300 -- store the highest degree of error
1301 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1302 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1303 l_overall_status := x_return_status;
1304 END IF;
1305 END IF;
1306
1307 EXIT WHEN (i = p_tclv_tbl.LAST);
1308 i := p_tclv_tbl.NEXT(i);
1309 END LOOP;
1310
1311 x_return_status := l_overall_status;
1312
1313 END IF;
1314
1315
1316 EXCEPTION
1317 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1318 NULL;
1319 WHEN OTHERS THEN
1320 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1321 ,p_msg_name => g_unexpected_error
1322 ,p_token1 => g_sqlcode_token
1323 ,p_token1_value => sqlcode
1324 ,p_token2 => g_sqlerrm_token
1325 ,p_token2_value => sqlerrm);
1326 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1327 END validate_trx_cntrct_lines;
1328
1329 END OKL_TRX_CONTRACTS_PVT;