[Home] [Help]
PACKAGE BODY: APPS.OKL_TRNS_ACC_DSTRS_PUB
Source
1 PACKAGE BODY OKL_TRNS_ACC_DSTRS_PUB AS
2 /* $Header: OKLPTABB.pls 120.6.12010000.3 2008/10/01 23:23:19 rkuttiya ship $ */
3
4 PROCEDURE add_language IS
5 BEGIN
6 -- okl_tab_pvt.add_language;
7 NULL;
8 END add_language;
9 ---------------------------------------------------------------------------
10 -- PROCEDURE validate_ae_line_type
11 ---------------------------------------------------------------------------
12 PROCEDURE validate_ae_line_type(
13 x_return_status OUT NOCOPY VARCHAR2,
14 p_tabv_rec IN tabv_rec_type
15 ) IS
16 l_dummy VARCHAR2(1) ;
17 BEGIN
18 x_return_status := OKC_API.G_RET_STS_SUCCESS;
19 IF (p_tabv_rec.ae_line_type IS NULL) OR (p_tabv_rec.ae_line_type = OKC_API.G_MISS_CHAR) THEN
20 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
21 p_msg_name => g_required_value,
22 p_token1 => g_col_name_token,
23 p_token1_value => 'AE_LINE_TYPE');
24
25 x_return_status := OKC_API.G_RET_STS_ERROR;
26 RAISE G_EXCEPTION_HALT_VALIDATION;
27 END IF;
28 l_dummy := OKL_ACCOUNTING_UTIL.VALIDATE_LOOKUP_CODE
29 (p_lookup_type => 'OKL_ACCOUNTING_LINE_TYPE',
30 p_lookup_code => p_tabv_rec.ae_line_type);
31 IF l_dummy = okl_api.g_false THEN
32 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
33 p_msg_name => g_invalid_value,
34 p_token1 => g_col_name_token,
35 p_token1_value => 'ae_line_type');
36 x_return_status := Okc_Api.G_RET_STS_ERROR;
37 RAISE G_EXCEPTION_HALT_VALIDATION;
38 END IF;
39 EXCEPTION
40 WHEN G_EXCEPTION_HALT_VALIDATION THEN
41 NULL;
42 WHEN OTHERS THEN
43 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
44 p_msg_name => g_required_value,
45 p_token1 => g_sqlcode_token,
46 p_token1_value => SQLCODE,
47 p_token2 => g_sqlerrm_token,
48 p_token2_value => SQLERRM);
49 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
50 END validate_ae_line_type;
51
52 ---------------------------------------------------------------------------
53 -- PROCEDURE validate_cr_dr_flag
54 ---------------------------------------------------------------------------
55 PROCEDURE validate_cr_dr_flag(
56 x_return_status OUT NOCOPY VARCHAR2,
57 p_tabv_rec IN tabv_rec_type
58 ) IS
59
60 l_dummy VARCHAR2(1) := OKC_API.G_FALSE;
61 l_app_id NUMBER := 101;
62 l_view_app_id NUMBER := 101;
63 BEGIN
64 x_return_status := OKC_API.G_RET_STS_SUCCESS;
65 IF (p_tabv_rec.cr_dr_flag IS NULL) OR (p_tabv_rec.cr_dr_flag = OKC_API.G_MISS_CHAR) THEN
66 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
67 p_msg_name => g_required_value,
68 p_token1 => g_col_name_token,
69 p_token1_value => 'cr_dr_flag');
70
71 x_return_status := OKC_API.G_RET_STS_ERROR;
72 RAISE G_EXCEPTION_HALT_VALIDATION;
73 END IF;
74 l_dummy := OKL_ACCOUNTING_UTIL.VALIDATE_LOOKUP_CODE
75 (p_lookup_type => 'CR_DR',
76 p_lookup_code => p_tabv_rec.cr_dr_flag,
77 p_app_id => l_app_id,
78 p_view_app_id => l_view_app_id);
79
80 IF (l_dummy = okc_api.G_FALSE) THEN
81 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
82 p_msg_name => g_invalid_value,
83 p_token1 => g_col_name_token,
84 p_token1_value => 'cr_dr_flag');
85 x_return_status := Okc_Api.G_RET_STS_ERROR;
86 RAISE G_EXCEPTION_HALT_VALIDATION;
87 END IF;
88 EXCEPTION
89 WHEN G_EXCEPTION_HALT_VALIDATION THEN
90 NULL;
91 WHEN OTHERS THEN
92 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
93 p_msg_name => g_required_value,
94 p_token1 => g_sqlcode_token,
95 p_token1_value => SQLCODE,
96 p_token2 => g_sqlerrm_token,
97 p_token2_value => SQLERRM);
98 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
99 END validate_cr_dr_flag;
100 ---------------------------------------------------------------------------
101 -- PROCEDURE validate_ccid
102 ---------------------------------------------------------------------------
103 PROCEDURE validate_ccid(
104 x_return_status OUT NOCOPY VARCHAR2,
105 p_tabv_rec IN tabv_rec_type
106 ) IS
107 l_dummy VARCHAR2(1) := okl_api.g_false;
108 BEGIN
109 x_return_status := OKC_API.G_RET_STS_SUCCESS;
110 IF (p_tabv_rec.code_combination_id IS NULL) OR (p_tabv_rec.code_combination_id = OKC_API.G_MISS_NUM) THEN
111 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
112 p_msg_name => g_required_value,
113 p_token1 => g_col_name_token,
114 p_token1_value => 'ACCOUNT');
115
116 x_return_status := OKC_API.G_RET_STS_ERROR;
117 RAISE G_EXCEPTION_HALT_VALIDATION;
118 END IF;
119 EXCEPTION
120 WHEN G_EXCEPTION_HALT_VALIDATION THEN
121 NULL;
122 WHEN OTHERS THEN
123 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
124 p_msg_name => g_required_value,
125 p_token1 => g_sqlcode_token,
126 p_token1_value => SQLCODE,
127 p_token2 => g_sqlerrm_token,
128 p_token2_value => SQLERRM);
129 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
130 END validate_ccid;
131 ---------------------------------------------------------------------------
132 -- FUNCTION validate_Attributes_for_ats
133 ---------------------------------------------------------------------------
134 FUNCTION validate_Attributes_for_ats (
135 p_tabv_rec IN tabv_rec_type
136 ) RETURN VARCHAR2 IS
137 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
138 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
139 BEGIN
140 validate_ccid(x_return_status, p_tabv_rec);
141 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
142 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
143 l_return_status := x_return_Status;
144 RAISE G_EXCEPTION_HALT_VALIDATION;
145 ELSE
146 l_return_status := x_return_status;
147 END IF;
148 END IF;
149 validate_cr_dr_flag(x_return_status, p_tabv_rec);
150 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
151 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
152 l_return_status := x_return_Status;
153 RAISE G_EXCEPTION_HALT_VALIDATION;
154 ELSE
155 l_return_status := x_return_status;
156 END IF;
157 END IF;
158 validate_ae_line_type(x_return_status, p_tabv_rec);
159 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
160 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
161 l_return_status := x_return_Status;
162 RAISE G_EXCEPTION_HALT_VALIDATION;
163 ELSE
164 l_return_status := x_return_status;
165 END IF;
166 END IF;
167 RETURN(l_return_status);
168 EXCEPTION
169 WHEN G_EXCEPTION_HALT_VALIDATION THEN
170 -- just come out with return status
171 NULL;
172 RETURN (l_return_status);
173
174 WHEN OTHERS THEN
175 -- store SQL error message on message stack for caller
176 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
177 p_msg_name => g_unexpected_error,
178 p_token1 => g_sqlcode_token,
179 p_token1_value => SQLCODE,
180 p_token2 => g_sqlerrm_token,
181 p_token2_value => SQLERRM);
182 -- notify caller of an UNEXPECTED error
183 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
184 RETURN(l_return_status);
185 END validate_Attributes_for_ats;
186
187 ---------------------------------------------------------------------------
188 -- PROCEDURE insert_trns_acc_dstrs
189 ---------------------------------------------------------------------------
190
191 PROCEDURE insert_trns_acc_dstrs(
192 p_api_version IN NUMBER
193 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
194 ,x_return_status OUT NOCOPY VARCHAR2
195 ,x_msg_count OUT NOCOPY NUMBER
196 ,x_msg_data OUT NOCOPY VARCHAR2
197 ,p_tabv_rec IN tabv_rec_type
198 ,x_tabv_rec OUT NOCOPY tabv_rec_type
199 ) IS
200 l_tabv_rec tabv_rec_type;
201 l_data VARCHAR2(100);
202 l_api_name CONSTANT VARCHAR2(30) := 'insert_trns_acc_dstrs';
203 l_count NUMBER ;
204 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
205 l_acct_der VARCHAR(4);
206 l_row_notfound BOOLEAN := FALSE;
207 item_not_found_error EXCEPTION;
208 CURSOR acc_temp_name_csr(template_id NUMBER) IS
209 SELECT NAME
210 FROM okl_ae_templates
211 WHERE id = template_id;
212 BEGIN
213 x_return_status := FND_API.G_RET_STS_SUCCESS;
214 SAVEPOINT insert_trns_acc_dstrs;
215 l_tabv_rec := p_tabv_rec;
216 -- Added as part of SLA Uptake Bug#5707866 by zrehman on 7-Feb-2006 start
217 IF(p_tabv_rec.template_id IS NOT NULL AND p_tabv_rec.template_id <> Okc_Api.G_MISS_NUM) THEN
218 IF(p_tabv_rec.accounting_template_name IS NULL OR p_tabv_rec.accounting_template_name = OKC_API.G_MISS_CHAR) THEN
219 OPEN acc_temp_name_csr(p_tabv_rec.template_id);
220 FETCH acc_temp_name_csr INTO l_tabv_rec.accounting_template_name;
221 l_row_notfound := acc_temp_name_csr%NOTFOUND;
222 IF (l_row_notfound) THEN
223 Okc_Api.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TEMPLATE_ID');
224 RAISE item_not_found_error;
225 END IF;
226 CLOSE acc_temp_name_csr;
227
228 END IF;
229 END IF;
230 l_acct_der := okl_Accounting_util.get_account_derivation;
231 IF(l_acct_der='ATS') THEN
232 l_return_status := validate_Attributes_for_ats(p_tabv_rec);
233 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
234 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
235 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
236 RAISE OKC_API.G_EXCEPTION_ERROR;
237 END IF;
238 END IF;
239
240 -- Added as part of SLA Uptake Bug#5707866 by zrehman on 7-Feb-2006 end
241 okl_tab_pvt.insert_row(
242 p_api_version => p_api_version
243 ,p_init_msg_list => p_init_msg_list
244 ,x_return_status => l_return_status
245 ,x_msg_count => x_msg_count
246 ,x_msg_data => x_msg_data
247 ,p_tabv_rec => l_tabv_rec
248 ,x_tabv_rec => x_tabv_rec
249 );
250
251 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
252 RAISE FND_API.G_EXC_ERROR;
253 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
255 END IF;
256
257 /* re-assign local record structure using output record from pvt api */
258 l_tabv_rec := x_tabv_rec;
259
260
261
262
263 EXCEPTION
264 WHEN FND_API.G_EXC_ERROR THEN
265 ROLLBACK TO insert_trns_acc_dstrs;
266 x_return_status := FND_API.G_RET_STS_ERROR;
267
268 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
269 ,p_count => x_msg_count
270 ,p_data => x_msg_data);
271
272 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
273 ROLLBACK TO insert_trns_acc_dstrs;
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
275 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
276 ,p_count => x_msg_count
277 ,p_data => x_msg_data);
278
279 WHEN OTHERS THEN
280 FND_MSG_PUB.ADD_EXC_MSG('OKL_TRNS_ACC_DSTRS_PUB','insert_trns_acc_dstrs');
281 -- store SQL error message on message stack for caller
282 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
283 ,p_count => x_msg_count
284 ,p_data => x_msg_data);
285 -- notify caller of an UNEXPECTED error
286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
287 END insert_trns_acc_dstrs;
288
289
290 PROCEDURE insert_trns_acc_dstrs(
291 p_api_version IN NUMBER
292 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
293 ,x_return_status OUT NOCOPY VARCHAR2
294 ,x_msg_count OUT NOCOPY NUMBER
295 ,x_msg_data OUT NOCOPY VARCHAR2
296 ,p_tabv_tbl IN tabv_tbl_type
297 ,x_tabv_tbl OUT NOCOPY tabv_tbl_type
298 ) IS
299 l_tabv_tbl tabv_tbl_type;
300 l_data VARCHAR2(100);
301 l_count NUMBER ;
302 l_api_name CONSTANT VARCHAR2(30) := 'insert_trns_acc_dstrs';
303 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
304 l_overall_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
305 i NUMBER;
306
307 BEGIN
308 x_return_status := FND_API.G_RET_STS_SUCCESS;
309 SAVEPOINT insert_trns_acc_dstrs;
310 l_tabv_tbl := p_tabv_tbl;
311
312
313
314 IF (p_tabv_tbl.COUNT > 0) THEN
315 i := p_tabv_tbl.FIRST;
316
317 LOOP
318 insert_trns_acc_dstrs (
319 p_api_version => p_api_version
320 ,p_init_msg_list => p_init_msg_list
321 ,x_return_status => x_return_status
322 ,x_msg_count => x_msg_count
323 ,x_msg_data => x_msg_data
324 ,p_tabv_rec => p_tabv_tbl(i)
325 ,x_tabv_rec => x_tabv_tbl(i)
326 );
327
328 -- store the highest degree of error
329 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
330 IF l_overall_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
331 l_overall_status := x_return_status;
332 END IF;
333 END IF;
334 EXIT WHEN (i = p_tabv_tbl.LAST);
335
336 i := p_tabv_tbl.NEXT(i);
337
338 END LOOP;
339
340 -- return overall status
341 x_return_status := l_overall_status;
342 END IF;
343
344 l_return_status := x_return_status;
345
346 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
347 RAISE FND_API.G_EXC_ERROR;
348 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
350 END IF;
351
352 /* re-assign local table structure using output table from pvt api */
353 l_tabv_tbl := x_tabv_tbl;
354
355
356
357 EXCEPTION
358 WHEN FND_API.G_EXC_ERROR THEN
359 ROLLBACK TO insert_trns_acc_dstrs;
360 x_return_status := FND_API.G_RET_STS_ERROR;
361
362 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
363 ,p_count => x_msg_count
364 ,p_data => x_msg_data);
365
366 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
367 ROLLBACK TO insert_trns_acc_dstrs;
368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
369 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
370 ,p_count => x_msg_count
371 ,p_data => x_msg_data);
372
373 WHEN OTHERS THEN
374 FND_MSG_PUB.ADD_EXC_MSG('OKL_TRNS_ACC_DSTRS_PUB','insert_trns_acc_dstrs');
375 -- store SQL error message on message stack for caller
376 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
377 ,p_count => x_msg_count
378 ,p_data => x_msg_data);
379 -- notify caller of an UNEXPECTED error
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
381 END insert_trns_acc_dstrs;
382
383 PROCEDURE lock_trns_acc_dstrs(
384 p_api_version IN NUMBER
385 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
386 ,x_return_status OUT NOCOPY VARCHAR2
387 ,x_msg_count OUT NOCOPY NUMBER
388 ,x_msg_data OUT NOCOPY VARCHAR2
389 ,p_tabv_rec IN tabv_rec_type
390 ) IS
391
392 l_tabv_rec tabv_rec_type;
393 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
394
395 BEGIN
396 x_return_status := FND_API.G_RET_STS_SUCCESS;
397 SAVEPOINT lock_trns_acc_dstrs;
398 l_tabv_rec := p_tabv_rec;
399
400 okl_tab_pvt.lock_row(
401 p_api_version => p_api_version
402 ,p_init_msg_list => p_init_msg_list
403 ,x_return_status => l_return_status
404 ,x_msg_count => x_msg_count
405 ,x_msg_data => x_msg_data
406 ,p_tabv_rec => l_tabv_rec
407 );
408
409 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
410 RAISE FND_API.G_EXC_ERROR;
411 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
413 END IF;
414
415 EXCEPTION
416 WHEN FND_API.G_EXC_ERROR THEN
417 ROLLBACK TO lock_trns_acc_dstrs;
418 x_return_status := FND_API.G_RET_STS_ERROR;
419
420 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
421 ,p_count => x_msg_count
422 ,p_data => x_msg_data);
423
424 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
425 ROLLBACK TO lock_trns_acc_dstrs;
426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
427 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
428 ,p_count => x_msg_count
429 ,p_data => x_msg_data);
430
431 WHEN OTHERS THEN
432 FND_MSG_PUB.ADD_EXC_MSG('OKL_TRNS_ACC_DSTRS_PUB','lock_trns_acc_dstrs');
433 -- store SQL error message on message stack for caller
434 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
435 ,p_count => x_msg_count
436 ,p_data => x_msg_data);
437 -- notify caller of an UNEXPECTED error
438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
439 END lock_trns_acc_dstrs;
440
441 PROCEDURE lock_trns_acc_dstrs(
442 p_api_version IN NUMBER
443 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
444 ,x_return_status OUT NOCOPY VARCHAR2
445 ,x_msg_count OUT NOCOPY NUMBER
446 ,x_msg_data OUT NOCOPY VARCHAR2
447 ,p_tabv_tbl IN tabv_tbl_type
448 ) IS
449
450 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
451 l_overall_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
452 i NUMBER;
453
454 BEGIN
455 x_return_status := FND_API.G_RET_STS_SUCCESS;
456 SAVEPOINT lock_trns_acc_dstrs;
457
458 IF (p_tabv_tbl.COUNT > 0) THEN
459 i := p_tabv_tbl.FIRST;
460
461 LOOP
462 lock_trns_acc_dstrs (
463 p_api_version => p_api_version
464 ,p_init_msg_list => p_init_msg_list
465 ,x_return_status => x_return_status
466 ,x_msg_count => x_msg_count
467 ,x_msg_data => x_msg_data
468 ,p_tabv_rec => p_tabv_tbl(i)
469 );
470
471 -- store the highest degree of error
472 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
473 IF l_overall_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
474 l_overall_status := x_return_status;
475 END IF;
476 END IF;
477 EXIT WHEN (i = p_tabv_tbl.LAST);
478
479 i := p_tabv_tbl.NEXT(i);
480
481 END LOOP;
482
483 -- return overall status
484 x_return_status := l_overall_status;
485 END IF;
486
487 l_return_status := x_return_status;
488
489 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
490 RAISE FND_API.G_EXC_ERROR;
491 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
493 END IF;
494
495 EXCEPTION
496 WHEN FND_API.G_EXC_ERROR THEN
497 ROLLBACK TO lock_trns_acc_dstrs;
498 x_return_status := FND_API.G_RET_STS_ERROR;
499
500 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
501 ,p_count => x_msg_count
502 ,p_data => x_msg_data);
503
504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
505 ROLLBACK TO lock_trns_acc_dstrs;
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
508 ,p_count => x_msg_count
509 ,p_data => x_msg_data);
510
511 WHEN OTHERS THEN
512 FND_MSG_PUB.ADD_EXC_MSG('OKL_TRNS_ACC_DSTRS_PUB','lock_trns_acc_dstrs');
513 -- store SQL error message on message stack for caller
514 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
515 ,p_count => x_msg_count
516 ,p_data => x_msg_data);
517 -- notify caller of an UNEXPECTED error
518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
519 END lock_trns_acc_dstrs;
520
521 PROCEDURE update_trns_acc_dstrs(
522 p_api_version IN NUMBER
523 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
524 ,x_return_status OUT NOCOPY VARCHAR2
525 ,x_msg_count OUT NOCOPY NUMBER
526 ,x_msg_data OUT NOCOPY VARCHAR2
527 ,p_tabv_rec IN tabv_rec_type
528 ,x_tabv_rec OUT NOCOPY tabv_rec_type
529 ) IS
530 l_tabv_rec tabv_rec_type;
531 l_data VARCHAR2(100);
532 l_count NUMBER ;
533 l_api_name CONSTANT VARCHAR2(30) := 'update_trns_acc_dstrs';
534 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
535 BEGIN
536 x_return_status := FND_API.G_RET_STS_SUCCESS;
537 SAVEPOINT update_trns_acc_dstrs;
538 l_tabv_rec := p_tabv_rec;
539
540
541
542 okl_tab_pvt.update_row(
543 p_api_version => p_api_version
544 ,p_init_msg_list => p_init_msg_list
545 ,x_return_status => l_return_status
546 ,x_msg_count => x_msg_count
547 ,x_msg_data => x_msg_data
548 ,p_tabv_rec => l_tabv_rec
549 ,x_tabv_rec => x_tabv_rec
550 );
551
552 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
553 RAISE FND_API.G_EXC_ERROR;
554 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
556 END IF;
557
558 /* re-assign local record structure using output record from pvt api */
559 l_tabv_rec := x_tabv_rec;
560
561
562
563 EXCEPTION
564 WHEN FND_API.G_EXC_ERROR THEN
565 ROLLBACK TO update_trns_acc_dstrs;
566 x_return_status := FND_API.G_RET_STS_ERROR;
567
568 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
569 ,p_count => x_msg_count
570 ,p_data => x_msg_data);
571
572 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
573 ROLLBACK TO update_trns_acc_dstrs;
574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
575 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
576 ,p_count => x_msg_count
577 ,p_data => x_msg_data);
578
579 WHEN OTHERS THEN
580 FND_MSG_PUB.ADD_EXC_MSG('OKL_TRNS_ACC_DSTRS_PUB','update_trns_acc_dstrs');
581 -- store SQL error message on message stack for caller
582 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
583 ,p_count => x_msg_count
584 ,p_data => x_msg_data);
585 -- notify caller of an UNEXPECTED error
586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
587 END update_trns_acc_dstrs;
588
589
590 PROCEDURE update_trns_acc_dstrs(
591 p_api_version IN NUMBER
592 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
593 ,x_return_status OUT NOCOPY VARCHAR2
594 ,x_msg_count OUT NOCOPY NUMBER
595 ,x_msg_data OUT NOCOPY VARCHAR2
596 ,p_tabv_tbl IN tabv_tbl_type
597 ,x_tabv_tbl OUT NOCOPY tabv_tbl_type
598 ) IS
599 l_tabv_tbl tabv_tbl_type;
600 l_data VARCHAR2(100);
601 l_count NUMBER;
602 l_api_name CONSTANT VARCHAR2(30) := 'update_trns_acc_dstrs';
603 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
604 l_overall_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
605 i NUMBER;
606
607 BEGIN
608 x_return_status := FND_API.G_RET_STS_SUCCESS;
609 SAVEPOINT update_trns_acc_dstrs;
610 l_tabv_tbl := p_tabv_tbl;
611
612
613
614 IF (p_tabv_tbl.COUNT > 0) THEN
615 i := p_tabv_tbl.FIRST;
616
617 LOOP
618 update_trns_acc_dstrs (
619 p_api_version => p_api_version
620 ,p_init_msg_list => p_init_msg_list
621 ,x_return_status => x_return_status
622 ,x_msg_count => x_msg_count
623 ,x_msg_data => x_msg_data
624 ,p_tabv_rec => p_tabv_tbl(i)
625 ,x_tabv_rec => x_tabv_tbl(i)
626 );
627
628 -- store the highest degree of error
629 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
630 IF l_overall_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
631 l_overall_status := x_return_status;
632 END IF;
633 END IF;
634 EXIT WHEN (i = p_tabv_tbl.LAST);
635
636 i := p_tabv_tbl.NEXT(i);
637
638 END LOOP;
639
640 -- return overall status
641 x_return_status := l_overall_status;
642 END IF;
643
644 l_return_status := x_return_status;
645
646 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
647 RAISE FND_API.G_EXC_ERROR;
648 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
650 END IF;
651
652 /* re-assign local table structure using output table from pvt api */
653 l_tabv_tbl := x_tabv_tbl;
654
655
656
657 EXCEPTION
658 WHEN FND_API.G_EXC_ERROR THEN
659 ROLLBACK TO update_trns_acc_dstrs;
660 x_return_status := FND_API.G_RET_STS_ERROR;
661
662 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
663 ,p_count => x_msg_count
664 ,p_data => x_msg_data);
665
666 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
667 ROLLBACK TO update_trns_acc_dstrs;
668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
669 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
670 ,p_count => x_msg_count
671 ,p_data => x_msg_data);
672
673 WHEN OTHERS THEN
674 FND_MSG_PUB.ADD_EXC_MSG('OKL_TRNS_ACC_DSTRS_PUB','update_trns_acc_dstrs');
675 -- store SQL error message on message stack for caller
676 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
677 ,p_count => x_msg_count
678 ,p_data => x_msg_data);
679 -- notify caller of an UNEXPECTED error
680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
681 END update_trns_acc_dstrs;
682
683 PROCEDURE delete_trns_acc_dstrs(
684 p_api_version IN NUMBER
685 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
686 ,x_return_status OUT NOCOPY VARCHAR2
687 ,x_msg_count OUT NOCOPY NUMBER
688 ,x_msg_data OUT NOCOPY VARCHAR2
689 ,p_tabv_rec IN tabv_rec_type
690 ) IS
691 l_tabv_rec tabv_rec_type;
692 l_data VARCHAR2(100);
693 l_count NUMBER ;
694 l_api_name CONSTANT VARCHAR2(30) := 'delete_trns_acc_dstrs';
695 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
696
697
698 -- Added by Santonyr to fix the bug 3089327 on 07th Aug, 2003
699
700 l_tcn_id OKL_TRX_CONTRACTS.ID%TYPE;
701 l_tsu_code OKL_TRX_CONTRACTS.TSU_CODE%TYPE;
702
703 CURSOR tcl_csr(v_source_id NUMBER) IS
704 SELECT tcn_id
705 FROM OKL_TXL_CNTRCT_LNS
706 WHERE ID = v_source_id;
707
708 CURSOR tcn_csr(v_tcn_id NUMBER) IS
709 SELECT tsu_code
710 FROM OKL_TRX_CONTRACTS
711 WHERE ID = v_tcn_id;
712 --
713 BEGIN
714 x_return_status := FND_API.G_RET_STS_SUCCESS;
715 SAVEPOINT delete_trns_acc_dstrs;
716 l_tabv_rec := p_tabv_rec;
717
718
719
720 -- Added by Santonyr to fix the bug 3089327 on 07th Aug, 2003
721
722 OPEN tcl_csr(l_tabv_rec.source_id);
723 FETCH tcl_csr INTO l_tcn_id;
724 CLOSE tcl_csr;
725
726 OPEN tcn_csr(l_tcn_id);
727 FETCH tcn_csr INTO l_tsu_code;
728 CLOSE tcn_csr;
729
730 IF (l_tsu_code = 'CANCELED') THEN
731
732 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
733 p_msg_name => 'OKL_TRX_CANCELED');
734
735 RAISE OKL_API.G_EXCEPTION_ERROR;
736
737 END IF;
738
739
740 okl_tab_pvt.delete_row(
741 p_api_version => p_api_version
742 ,p_init_msg_list => p_init_msg_list
743 ,x_return_status => l_return_status
744 ,x_msg_count => x_msg_count
745 ,x_msg_data => x_msg_data
746 ,p_tabv_rec => l_tabv_rec
747 );
748
749 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
750 RAISE FND_API.G_EXC_ERROR;
751 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
752 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
753 END IF;
754
755
756
757 EXCEPTION
758 WHEN FND_API.G_EXC_ERROR THEN
759 ROLLBACK TO delete_trns_acc_dstrs;
760 x_return_status := FND_API.G_RET_STS_ERROR;
761
762 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
763 ,p_count => x_msg_count
764 ,p_data => x_msg_data);
765
766 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
767 ROLLBACK TO delete_trns_acc_dstrs;
768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
769 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
770 ,p_count => x_msg_count
771 ,p_data => x_msg_data);
772
773 WHEN OTHERS THEN
774 FND_MSG_PUB.ADD_EXC_MSG('OKL_TRNS_ACC_DSTRS_PUB','delete_trns_acc_dstrs');
775 -- store SQL error message on message stack for caller
776 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
777 ,p_count => x_msg_count
778 ,p_data => x_msg_data);
779 -- notify caller of an UNEXPECTED error
780 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
781 END delete_trns_acc_dstrs;
782
783
784 PROCEDURE delete_trns_acc_dstrs(
785 p_api_version IN NUMBER
786 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
787 ,x_return_status OUT NOCOPY VARCHAR2
788 ,x_msg_count OUT NOCOPY NUMBER
789 ,x_msg_data OUT NOCOPY VARCHAR2
790 ,p_tabv_tbl IN tabv_tbl_type
791 ) IS
792 l_tabv_tbl tabv_tbl_type;
793 l_data VARCHAR2(100);
794 l_count NUMBER ;
795 l_api_name CONSTANT VARCHAR2(30) := 'delete_trns_acc_dstrs';
796 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
797 l_overall_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
798 i NUMBER;
799
800 BEGIN
801 x_return_status := FND_API.G_RET_STS_SUCCESS;
802 SAVEPOINT delete_trns_acc_dstrs;
803 l_tabv_tbl := p_tabv_tbl;
804
805
806
807 IF (p_tabv_tbl.COUNT > 0) THEN
808 i := p_tabv_tbl.FIRST;
809
810 LOOP
811 delete_trns_acc_dstrs (
812 p_api_version => p_api_version
813 ,p_init_msg_list => p_init_msg_list
814 ,x_return_status => x_return_status
815 ,x_msg_count => x_msg_count
816 ,x_msg_data => x_msg_data
817 ,p_tabv_rec => p_tabv_tbl(i)
818 );
819
820 -- store the highest degree of error
821 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
822 IF l_overall_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
823 l_overall_status := x_return_status;
824 END IF;
825 END IF;
826 EXIT WHEN (i = p_tabv_tbl.LAST);
827
828 i := p_tabv_tbl.NEXT(i);
829
830 END LOOP;
831
832 -- return overall status
833 x_return_status := l_overall_status;
834 END IF;
835
836 l_return_status := x_return_status;
837
838 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
839 RAISE FND_API.G_EXC_ERROR;
840 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
841 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
842 END IF;
843
844
845
846 EXCEPTION
847 WHEN FND_API.G_EXC_ERROR THEN
848 ROLLBACK TO delete_trns_acc_dstrs;
849 x_return_status := FND_API.G_RET_STS_ERROR;
850
851 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
852 ,p_count => x_msg_count
853 ,p_data => x_msg_data);
854
855 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
856 ROLLBACK TO delete_trns_acc_dstrs;
857 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
858 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
859 ,p_count => x_msg_count
860 ,p_data => x_msg_data);
861
862 WHEN OTHERS THEN
863 FND_MSG_PUB.ADD_EXC_MSG('OKL_TRNS_ACC_DSTRS_PUB','delete_trns_acc_dstrs');
864 -- store SQL error message on message stack for caller
865 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
866 ,p_count => x_msg_count
867 ,p_data => x_msg_data);
868 -- notify caller of an UNEXPECTED error
869 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
870 END delete_trns_acc_dstrs;
871
872 PROCEDURE validate_trns_acc_dstrs(
873 p_api_version IN NUMBER
874 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
875 ,x_return_status OUT NOCOPY VARCHAR2
876 ,x_msg_count OUT NOCOPY NUMBER
877 ,x_msg_data OUT NOCOPY VARCHAR2
878 ,p_tabv_rec IN tabv_rec_type
879 ) IS
880 l_tabv_rec tabv_rec_type;
881 l_data VARCHAR2(100);
882 l_count NUMBER;
883 l_api_name CONSTANT VARCHAR2(30) := 'validate_trns_acc_dstrs';
884 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
885 BEGIN
886 x_return_status := FND_API.G_RET_STS_SUCCESS;
887 SAVEPOINT validate_trns_acc_dstrs;
888 l_tabv_rec := p_tabv_rec;
889
890
891
892 okl_tab_pvt.validate_row(
893 p_api_version => p_api_version
894 ,p_init_msg_list => p_init_msg_list
895 ,x_return_status => l_return_status
896 ,x_msg_count => x_msg_count
897 ,x_msg_data => x_msg_data
898 ,p_tabv_rec => l_tabv_rec
899 );
900
901 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
902 RAISE FND_API.G_EXC_ERROR;
903 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
904 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
905 END IF;
906
907
908
909 EXCEPTION
910 WHEN FND_API.G_EXC_ERROR THEN
911 ROLLBACK TO validate_trns_acc_dstrs;
912 x_return_status := FND_API.G_RET_STS_ERROR;
913
914 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
915 ,p_count => x_msg_count
916 ,p_data => x_msg_data);
917
918 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
919 ROLLBACK TO validate_trns_acc_dstrs;
920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
921 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
922 ,p_count => x_msg_count
923 ,p_data => x_msg_data);
924
925 WHEN OTHERS THEN
926 FND_MSG_PUB.ADD_EXC_MSG('OKL_TRNS_ACC_DSTRS_PUB','validate_trns_acc_dstrs');
927 -- store SQL error message on message stack for caller
928 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
929 ,p_count => x_msg_count
930 ,p_data => x_msg_data);
931 -- notify caller of an UNEXPECTED error
932 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
933 END validate_trns_acc_dstrs;
934
935
936 PROCEDURE validate_trns_acc_dstrs(
937 p_api_version IN NUMBER
938 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
939 ,x_return_status OUT NOCOPY VARCHAR2
940 ,x_msg_count OUT NOCOPY NUMBER
941 ,x_msg_data OUT NOCOPY VARCHAR2
942 ,p_tabv_tbl IN tabv_tbl_type
943 ) IS
944 l_tabv_tbl tabv_tbl_type;
945 l_data VARCHAR2(100);
946 l_count NUMBER;
947 l_api_name CONSTANT VARCHAR2(30) := 'validate_trns_acc_dstrs';
948 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
949 l_overall_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
950 i NUMBER;
951
952 BEGIN
953 x_return_status := FND_API.G_RET_STS_SUCCESS;
954 SAVEPOINT validate_trns_acc_dstrs;
955 l_tabv_tbl := p_tabv_tbl;
956
957
958
959 IF (p_tabv_tbl.COUNT > 0) THEN
960 i := p_tabv_tbl.FIRST;
961
962 LOOP
963 validate_trns_acc_dstrs (
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_tabv_rec => p_tabv_tbl(i)
970 );
971
972 -- store the highest degree of error
973 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
974 IF l_overall_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
975 l_overall_status := x_return_status;
976 END IF;
977 END IF;
978 EXIT WHEN (i = p_tabv_tbl.LAST);
979
980 i := p_tabv_tbl.NEXT(i);
981
982 END LOOP;
983
984 -- return overall status
985 x_return_status := l_overall_status;
986 END IF;
987
988 l_return_status := x_return_status;
989
990 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
991 RAISE FND_API.G_EXC_ERROR;
992 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
994 END IF;
995
996
997
998
999 EXCEPTION
1000 WHEN FND_API.G_EXC_ERROR THEN
1001 ROLLBACK TO validate_trns_acc_dstrs;
1002 x_return_status := FND_API.G_RET_STS_ERROR;
1003
1004 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
1005 ,p_count => x_msg_count
1006 ,p_data => x_msg_data);
1007
1008 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1009 ROLLBACK TO validate_trns_acc_dstrs;
1010 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1011 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
1012 ,p_count => x_msg_count
1013 ,p_data => x_msg_data);
1014
1015 WHEN OTHERS THEN
1016 FND_MSG_PUB.ADD_EXC_MSG('OKL_TRNS_ACC_DSTRS_PUB','validate_trns_acc_dstrs');
1017 -- store SQL error message on message stack for caller
1018 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
1019 ,p_count => x_msg_count
1020 ,p_data => x_msg_data);
1021 -- notify caller of an UNEXPECTED error
1022 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1023 END validate_trns_acc_dstrs;
1024
1025
1026 END OKL_TRNS_ACC_DSTRS_PUB;