1 PACKAGE BODY ecx_code_conversion_pvt AS
2 -- $Header: ECXXREFB.pls 120.2.12020000.4 2013/03/21 13:03:23 jmaddila ship $
3
4 l_procedure PLS_INTEGER := ecx_debug.g_procedure;
5 l_statement PLS_INTEGER := ecx_debug.g_statement;
6 l_unexpected PLS_INTEGER := ecx_debug.g_unexpected;
7 l_procedureEnabled boolean := ecx_debug.g_procedureEnabled;
8 l_statementEnabled boolean := ecx_debug.g_statementEnabled;
9 l_unexpectedEnabled boolean := ecx_debug.g_unexpectedEnabled;
10
11 PROCEDURE populate_plsql_tbl_with_extval
12 (p_api_version_number IN NUMBER,
13 p_init_msg_list IN VARCHAR2 := G_FALSE,
14 p_simulate IN VARCHAR2 := G_FALSE,
15 p_commit IN VARCHAR2 := G_FALSE,
16 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
17 p_standard_id IN NUMBER,
18 p_return_status OUT NOCOPY VARCHAR2,
19 p_msg_count OUT NOCOPY PLS_INTEGER,
20 p_msg_data OUT NOCOPY VARCHAR2,
21 p_level IN PLS_INTEGER,
22 p_tbl IN OUT NOCOPY ecx_utils.dtd_node_tbl,
23 p_tp_id IN PLS_INTEGER) IS
24
25 i_method_name varchar2(2000) := 'ecx_code_conversion_pvt.populate_plsql_tbl_with_extval';
26
27 l_api_name CONSTANT VARCHAR2(30) := 'populate_plsql_tbl_with_extval';
28 l_api_version_number CONSTANT NUMBER := 1.0;
29
30 l_msg_count PLS_INTEGER;
31 l_msg_data VARCHAR2(2000);
32 l_ext_val VARCHAR2(4000) := NULL;
33 l_var_value VARCHAR2(4000) ;
34 i PLS_INTEGER;
35 xref_success CONSTANT VARCHAR2(1) := 0;
36 xref_warning CONSTANT VARCHAR2(1) := 1;
37
38 l_standard_id NUMBER; /* Bug 2110362
39 The standard_id for the custom
40 standard. */
41 l_univ_std_id pls_integer;
42 l_standard_type Varchar2(200);
43
44 BEGIN
45 if (l_procedureEnabled) then
46 ecx_debug.push(i_method_name);
47 end if;
48 if(l_statementEnabled) then
49 ecx_debug.log(l_statement, 'API version : ',p_api_version_number,i_method_name);
50 ecx_debug.log(l_statement, 'p_init_msg_list: ',p_init_msg_list,i_method_name);
51 ecx_debug.log(l_statement, 'p_simulate: ',p_simulate,i_method_name);
52 ecx_debug.log(l_statement, 'p_commit: ',p_commit,i_method_name);
53 ecx_debug.log(l_statement, 'p_validation_level: ',p_validation_level,i_method_name);
54 end if;
55
56 -- Standard Start of API savepoint
57 SAVEPOINT populate_plsql_tbl_ext_PVT;
58
59 -- Initialize API return status to success
60 p_return_status := g_ret_sts_success;
61
62 begin
63 select standard_type
64 into l_standard_type
65 from ecx_standards
66 where standard_id = p_standard_id;
67 exception
68 when others then
69 l_standard_type := 'XML';
70 end;
71
72 /* Bug 2110362
73 The Code conversion now has the following approach,
74 1. Obtain standard id meant for the standard CUSTOM.
75 2. Determine if code conversion is defined for CUSTOM.
76 If yes, return the value defined.
77 3. If No, determine if conversion is defined for the
78 transaction's standard (based on p_standard) and return
79 the value accordingly. */
80
81 BEGIN
82 SELECT standard_id
83 INTO l_standard_id
84 FROM ecx_standards
85 WHERE standard_code = 'CUSTOM'
86 and standard_type = l_standard_type;
87 EXCEPTION
88 WHEN OTHERS THEN
89 l_standard_id := -1;
90 END;
91
92 i := ecx_utils.g_source_levels(p_level).file_start_pos;
93 LOOP
94 IF (ecx_utils.g_source(i).external_level = p_level) then
95 if(l_statementEnabled) then
96 ecx_debug.log(l_statement, 'Interface Column Name', p_tbl(i).attribute_name,i_method_name);
97 end if;
98
99 if (p_tbl(i).is_clob is null) then
100 if(l_statementEnabled) then
101 ecx_debug.log(l_statement,'Value before code conversion:', p_tbl(i).value,i_method_name);
102 end if;
103 l_var_value := p_tbl(i).value;
104
105 IF p_tbl(i).xref_category_id IS NOT NULL THEN
106 BEGIN
107 SELECT xref_ext_value
108 INTO l_ext_val
109 FROM ecx_xref_dtl
110 WHERE tp_header_id = p_tp_id AND
111 xref_int_value = p_tbl(i).value AND
112 standard_id = l_standard_id AND
113 xref_category_id = p_tbl(i).xref_category_id AND
114 direction = 'OUT';
115
116 p_tbl(i).value := nvl(l_ext_val,l_var_value);
117 p_tbl(i).xref_retcode := xref_success;
118
119 EXCEPTION
120 WHEN no_data_found then
121 null;
122 BEGIN
123 SELECT xref_std_value
124 INTO l_ext_val
125 FROM ecx_xref_standards
126 WHERE standard_id = p_standard_id
127 AND xref_category_id = p_tbl(i).xref_category_id
128 AND xref_int_value = p_tbl(i).value;
129 if(l_statementEnabled) then
130 ecx_debug.log(l_statement,'Using Standard Conversion',i_method_name);
131 end if;
132 p_tbl(i).value := nvl(l_ext_val,l_var_value);
133 p_tbl(i).xref_retcode := xref_success;
134 EXCEPTION
135 when no_data_found then
136 BEGIN
137 SELECT standard_id
138 INTO l_univ_std_id
139 FROM ecx_standards
140 WHERE standard_code = 'UNIVERSAL'
141 and standard_type = l_standard_type;
142 EXCEPTION
143 WHEN OTHERS THEN
144 l_univ_std_id := -1;
145 END;
146 begin
147 SELECT xref_std_value
148 INTO l_ext_val
149 FROM ecx_xref_standards
150 WHERE standard_id = l_univ_std_id
151 AND xref_category_id = p_tbl(i).xref_category_id
152 AND xref_int_value = p_tbl(i).value;
153 if(l_statementEnabled) then
154 ecx_debug.log(l_statement,'Using Universal Conversion',i_method_name);
155 end if;
156 p_tbl(i).value := nvl(l_ext_val,l_var_value);
157 p_tbl(i).xref_retcode := xref_success;
158 EXCEPTION
159 /* Start of changes for Bug #2242061 */
160 WHEN TOO_MANY_ROWS THEN
161 if(l_unexpectedEnabled) then
162 ecx_debug.log(l_unexpected,'Data is Corrupted, Cannot resolve a unique' ||
163 ' universal code conversion value for '||
164 p_tbl(i).attribute_name, l_var_value,i_method_name);
165 end if;
166 p_tbl(i).value := l_var_value;
167 p_tbl(i).xref_retcode := xref_warning;
168 p_return_status := ecx_code_conversion_PVT.g_xref_not_found;
169 /* End of changes for Bug #2242061 */
170 WHEN OTHERS THEN
171 p_tbl(i).value := l_var_value;
172 p_tbl(i).xref_retcode := xref_warning;
173 p_return_status := ecx_code_conversion_PVT.g_xref_not_found;
174 END;
175 /* Start of changes for Bug #2242061 */
176 WHEN TOO_MANY_ROWS THEN
177 if(l_unexpectedEnabled) then
178 ecx_debug.log(l_unexpected, 'Data is Corrupted, Cannot resolve a unique' ||
179 ' standard code conversion value for '||
180 p_tbl(i).attribute_name, l_var_value,i_method_name);
181 end if;
182 p_tbl(i).value := l_var_value;
183 p_tbl(i).xref_retcode := xref_warning;
184 p_return_status := ecx_code_conversion_PVT.g_xref_not_found;
185 WHEN OTHERS THEN
186 p_tbl(i).value := l_var_value;
187 p_tbl(i).xref_retcode := xref_warning;
188 p_return_status := ecx_code_conversion_PVT.g_xref_not_found;
189 end;
190 WHEN TOO_MANY_ROWS THEN
191 if(l_unexpectedEnabled) then
192 ecx_debug.log(l_unexpected, 'Data is corrupted, Cannot resolve a unique custom code' ||
193 ' conversion value for' || p_tbl(i).attribute_name,l_var_value,i_method_name);
194 end if;
195 p_tbl(i).value := l_var_value;
196 p_tbl(i).xref_retcode := xref_warning;
197 p_return_status := ecx_code_conversion_PVT.g_xref_not_found;
198 WHEN OTHERS THEN
199 p_tbl(i).value := l_var_value;
200 p_tbl(i).xref_retcode := xref_warning;
201 p_return_status := ecx_code_conversion_PVT.g_xref_not_found;
202
203 /* End of changes for Bug #2242061 */
204 END;
205 END IF;
206 if(l_statementEnabled) then
207 ecx_debug.log(l_statement, 'Value after code conversion:', p_tbl(i).value,i_method_name);
208 ecx_debug.log(l_statement, 'Code conversion return code:', p_tbl(i).xref_retcode,i_method_name);
209 end if;
210 END IF;
211 END IF;
212 EXIT WHEN i= ecx_utils.g_source_levels(p_level).file_end_pos;
213 i := ecx_utils.g_source.next(i);
214 END LOOP;
215
216 if (l_procedureEnabled) then
217 ecx_debug.pop(i_method_name);
218 end if;
219 EXCEPTION
220 WHEN g_exc_error THEN
221 ROLLBACK TO populate_plsql_tbl_ext_PVT;
222 p_return_status := g_ret_sts_error;
223 if (l_procedureEnabled) then
224 ecx_debug.pop(i_method_name);
225 end if;
226 WHEN g_exc_unexpected_error THEN
227 ROLLBACK TO populate_plsql_tbl_ext_PVT;
228 p_return_status := g_ret_sts_error;
229 if (l_procedureEnabled) then
230 ecx_debug.pop(i_method_name);
231 end if;
232 WHEN OTHERS THEN
233 ROLLBACK TO populate_plsql_tbl_ext_PVT;
234 if(l_unexpectedEnabled) then
235 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_CODE',i_method_name, 'ERROR_CODE',SQLCODE);
236 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
237 end if;
238 p_return_status := g_ret_sts_error;
239 if (l_procedureEnabled) then
240 ecx_debug.pop(i_method_name);
241 end if;
242 END populate_plsql_tbl_with_extval;
243
244
245 PROCEDURE populate_plsql_tbl_with_intval
246 (
247 p_api_version_number IN NUMBER,
248 p_init_msg_list IN VARCHAR2 := G_FALSE,
249 p_simulate IN VARCHAR2 := G_FALSE,
250 p_commit IN VARCHAR2 := G_FALSE,
251 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
252 p_standard_id IN NUMBER ,
253 p_return_status OUT NOCOPY VARCHAR2,
254 p_msg_count OUT NOCOPY PLS_INTEGER,
255 p_msg_data OUT NOCOPY VARCHAR2,
256 p_level IN PLS_INTEGER,
257 p_apps_tbl IN OUT NOCOPY ecx_utils.dtd_node_tbl,
258 p_tp_id IN PLS_INTEGER
259 ) IS
260
261 i_method_name varchar2(2000) := 'ecx_code_conversion_pvt.populate_plsql_tbl_with_intval';
262 l_api_name CONSTANT VARCHAR2(30) := 'populate_plsql_tbl_with_intval';
263 l_api_version_number CONSTANT NUMBER := 1.0;
264
265 l_msg_count NUMBER;
266 l_msg_data VARCHAR2(2000);
267
271 xref_success CONSTANT VARCHAR2(1) := 0;
268 l_int_val VARCHAR2(240) := NULL;
269 l_var_value VARCHAR2(500);
270 j PLS_INTEGER;
272 xref_warning CONSTANT VARCHAR2(1) := 1;
273
274 BEGIN
275 if (l_procedureEnabled) then
276 ecx_debug.push(i_method_name);
277 end if;
278 if(l_statementEnabled) then
279 ecx_debug.log(l_statement, 'API version : ',p_api_version_number,i_method_name);
280 ecx_debug.log(l_statement, 'p_init_msg_list: ',p_init_msg_list,i_method_name);
281 ecx_debug.log(l_statement, 'p_simulate: ',p_simulate,i_method_name);
282 ecx_debug.log(l_statement, 'p_commit: ',p_commit,i_method_name);
283 ecx_debug.log(l_statement, 'p_validation_level: ',p_validation_level,i_method_name);
284 end if;
285 -- Standard Start of API savepoint
286 SAVEPOINT populate_plsql_tbl_int_PVT;
287
288 -- Initialize API return status to success
289 p_return_status := g_ret_sts_success;
290
291 j := ecx_utils.g_source_levels(p_level).file_start_pos;
292 LOOP
293 IF (ecx_utils.g_source(j).external_level = p_level) THEN
294 if(l_statementEnabled) then
295 ecx_debug.log(l_statement,'Interface Column Name', p_apps_tbl(j).attribute_name,i_method_name);
296 ecx_debug.log(l_statement, 'Value before code conversion:', p_apps_tbl(j).value,i_method_name);
297 end if;
298 l_var_value := p_apps_tbl(j).value;
299
300 IF l_var_value IS NULL THEN
301 p_apps_tbl(j).value := l_var_value;
302 ELSIF p_apps_tbl(j).xref_category_id IS NOT NULL THEN
303 BEGIN
304 SELECT xref_int_value
305 INTO l_int_val
306 FROM ecx_xref_dtl
307 WHERE tp_header_id = p_tp_id AND
308 standard_id = p_standard_id AND
309 xref_category_id = p_apps_tbl(j).xref_category_id AND
310 xref_ext_value = p_apps_tbl(j).value AND
311 direction = 'IN';
312 p_apps_tbl(j).value := l_int_val;
313 p_apps_tbl(j).xref_retcode := xref_success;
314 EXCEPTION
315 WHEN NO_DATA_FOUND THEN
316 if(l_unexpectedEnabled) then
317 ecx_debug.log(l_unexpected, 'Using Standard Conversion',i_method_name);
318 end if;
319 null;
320 BEGIN
321 SELECT xref_std_value
322 INTO l_int_val
323 FROM ecx_xref_standards
324 WHERE standard_id = p_standard_id
325 AND xref_category_id = p_apps_tbl(j).xref_category_id
326 AND xref_int_value = p_apps_tbl(j).value;
327
328 p_apps_tbl(j).value := nvl(l_int_val,l_var_value);
329 p_apps_tbl(j).xref_retcode := xref_success;
330 EXCEPTION
331 WHEN OTHERS THEN
332 p_apps_tbl(j).value := l_var_value;
333 p_return_status := ecx_code_conversion_PVT.g_xref_not_found;
334 p_apps_tbl(j).xref_retcode := xref_warning;
335 END;
336 END;
337 END IF;
338
339 if(l_unexpectedEnabled) then
340 ecx_debug.log(l_unexpected, 'Value after code conversion:', p_apps_tbl(j).value,i_method_name);
341 ecx_debug.log(l_unexpected, 'Code conversion return code:', p_apps_tbl(j).xref_retcode,i_method_name);
342 end if;
343 END IF;
344
345 EXIT WHEN j= ecx_utils.g_source_levels(p_level).file_end_pos;
346 j := ecx_utils.g_source.next(j);
347 END LOOP;
348
349 -- *******************************************************
350 -- Standard check of p_simulate and p_commit parameters
351 -- *******************************************************
352
353 if (l_procedureEnabled) then
354 ecx_debug.pop(i_method_name);
355 end if;
356
357 EXCEPTION
358 WHEN g_exc_error THEN
359 ROLLBACK TO populate_plsql_tbl_int_PVT;
360 p_return_status := g_ret_sts_error;
361 if (l_procedureEnabled) then
362 ecx_debug.pop(i_method_name);
363 end if;
364 WHEN G_EXC_UNEXPECTED_ERROR THEN
365 ROLLBACK TO populate_plsql_tbl_int_PVT;
366 p_return_status := g_ret_sts_error;
367 if (l_procedureEnabled) then
368 ecx_debug.pop(i_method_name);
369 end if;
370 WHEN OTHERS THEN
371 if(l_unexpectedEnabled) then
372 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_CODE',i_method_name,'ERROR_CODE',SQLCODE);
373 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
374 end if;
375 ROLLBACK TO populate_plsql_tbl_int_PVT;
376 p_return_status := g_ret_sts_error;
377 if (l_procedureEnabled) then
378 ecx_debug.pop(i_method_name);
379 end if;
380 END populate_plsql_tbl_with_intval;
381
382 PROCEDURE convert_external_value
383 (
384 p_api_version_number IN NUMBER,
385 p_init_msg_list IN VARCHAR2 := G_FALSE,
386 p_simulate IN VARCHAR2 := G_FALSE,
387 p_commit IN VARCHAR2 := G_FALSE,
388 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
389 p_standard_id IN NUMBER,
390 p_return_status OUT NOCOPY VARCHAR2,
391 p_msg_count OUT NOCOPY PLS_INTEGER,
392 p_msg_data OUT NOCOPY VARCHAR2,
393 p_value IN OUT NOCOPY VARCHAR2,
394 p_category_id IN PLS_INTEGER,
395 p_snd_tp_id IN PLS_INTEGER,
396 p_rec_tp_id IN PLS_INTEGER
400 i_method_name varchar2(2000) := 'ecx_code_conversion_pvt.convert_external_value';
397 ) IS
398
399
401
402 l_api_name CONSTANT VARCHAR2(30) := 'convert_external_value';
403 l_api_version_number CONSTANT NUMBER := 1.0;
404
405 l_msg_count NUMBER;
406 l_msg_data VARCHAR2(2000);
407 l_int_val VARCHAR2(4000) := NULL;
408 l_ext_val VARCHAR2(4000) := NULL;
409 l_var_value VARCHAR2(4000);
410 l_standard_id NUMBER; /* Bug 2110362
411 The standard_id for the custom
412 standard. */
413 l_univ_std_id pls_integer;
414 l_standard_type varchar2(200);
415
416 BEGIN
417 if (l_procedureEnabled) then
418 ecx_debug.push(i_method_name);
419 end if;
420 if(l_statementEnabled) then
421 ecx_debug.log(l_statement, 'API version : ',p_api_version_number,i_method_name);
422 ecx_debug.log(l_statement, 'p_value: ',p_value,i_method_name);
423 ecx_debug.log(l_statement, 'p_category_id: ',p_category_id,i_method_name);
424 ecx_debug.log(l_statement, 'p_snd_tp_id: ',p_snd_tp_id,i_method_name);
425 ecx_debug.log(l_statement, 'p_rec_tp_id: ',p_rec_tp_id,i_method_name);
426 end if;
427
428 -- Standard Start of API savepoint
429 SAVEPOINT populate_value_extpt_PVT;
430
431 -- Initialize API return status to success
432 p_return_status := g_ret_sts_success;
433
434 if(l_statementEnabled) then
435 ecx_debug.log(l_statement,'Value before code conversion:', p_value,i_method_name);
436 end if;
437 l_var_value := p_value;
438
439 begin
440 select standard_type
441 into l_standard_type
442 from ecx_standards
443 where standard_id = p_standard_id;
444 exception
445 when others then
446 l_standard_type := 'XML';
447 end;
448
449 /* Bug 2110362
450 The Code conversion now has the following approach,
451 1. Obtain standard id meant for the standard CUSTOM.
452 2. Determine if code conversion is defined for CUSTOM.
453 If yes, return the value defined.
454 3. If No, determine if conversion is defined for the
455 transaction's standard (based on p_standard) and return
456 the value accordingly. */
457
458 BEGIN
459 SELECT standard_id
460 INTO l_standard_id
461 FROM ecx_standards
462 WHERE standard_code = 'CUSTOM'
463 and standard_type = l_standard_type;
464 EXCEPTION
465 WHEN OTHERS THEN
466 l_standard_id := -1;
467 END;
468
469 IF p_value IS NOT NULL THEN
470 BEGIN
471 /* Bug 2110362
472 Replaced reference to p_standard_id with l_standard_id. */
473 SELECT xref_int_value
474 INTO l_int_val
475 FROM ecx_xref_dtl
476 WHERE tp_header_id = p_snd_tp_id AND
477 standard_id = l_standard_id AND
478 xref_category_id = p_category_id AND
479 xref_ext_value = p_value AND
480 direction = 'IN';
481 p_return_status := 0;
482 EXCEPTION
483 WHEN no_data_found then
484 begin
485 SELECT xref_int_value
486 INTO l_int_val
487 FROM ecx_xref_standards
488 WHERE standard_id = p_standard_id
489 AND xref_category_id = p_category_id
490 AND xref_std_value = rtrim(p_value);
491 p_return_status := 0;
492 exception
493 when no_data_found then
494 BEGIN
495 SELECT standard_id
496 INTO l_univ_std_id
497 FROM ecx_standards
498 WHERE standard_code = 'UNIVERSAL'
499 and standard_type = l_standard_type;
500 EXCEPTION
501 WHEN OTHERS THEN
502 l_univ_std_id := -1;
503 END;
504 begin
505 SELECT xref_int_value
506 INTO l_int_val
507 FROM ecx_xref_standards
508 WHERE standard_id = l_univ_std_id
509 AND xref_category_id = p_category_id
510 AND xref_std_value = rtrim(p_value);
511 p_return_status := 0;
512 exception
513 when others then
514 p_return_status := ecx_code_conversion_PVT.g_xref_not_found;
515 p_value := l_var_value;
516 l_int_val := l_var_value;
517 --ecx_debug.pop('ecx_code_conversion_PVT.convert_external_value');
518 --return;
519 end;
520 end;
521
522 END;
523 p_value := l_int_val;
524 if(l_statementEnabled) then
528 IF (l_int_val IS NOT NULL AND p_rec_tp_id IS NOT NULL) THEN
525 ecx_debug.log(l_statement,'Value after ext to int code conversion:', p_value,i_method_name);
526 end if;
527
529 BEGIN
530 /* Bug 2110362
531 Replaced reference to p_standard_id with
532 l_standard_id. */
533 SELECT xref_ext_value
534 INTO l_ext_val
535 FROM ecx_xref_dtl
536 WHERE tp_header_id = p_rec_tp_id AND
537 standard_id = l_standard_id AND
538 xref_category_id = p_category_id AND
539 xref_int_value = l_int_val AND
540 direction = 'OUT';
541 p_return_status := 0;
542 EXCEPTION
543 WHEN no_data_found then
544 begin
545 SELECT xref_std_value
546 INTO l_ext_val
547 FROM ecx_xref_standards
548 WHERE standard_id = p_standard_id
549 AND xref_category_id = p_category_id
550 AND xref_int_value = p_value;
551 p_return_status := 0;
552 exception
553 when no_data_found then
554 begin
555 SELECT xref_std_value
556 INTO l_ext_val
557 FROM ecx_xref_standards
558 WHERE standard_id = l_univ_std_id
559 AND xref_category_id = p_category_id
560 AND xref_int_value = p_value;
561 p_return_status := 0;
562 exception
563 when others then
564 p_return_status := ecx_code_conversion_PVT.g_recv_xref_not_found;
565 if (l_procedureEnabled) then
566 ecx_debug.pop(i_method_name);
567 end if;
568 return;
569 end;
570 end;
571
572 END;
573 p_value := l_ext_val;
574 if(l_unexpectedEnabled) then
575 ecx_debug.log(l_unexpected, 'Value after int to ext code conversion:',
576 p_value,i_method_name);
577 end if;
578 END IF;
579 END IF;
580 if(l_statementEnabled) then
581 ecx_debug.log(l_statement, 'Value after code conversion:', p_value,i_method_name);
582 end if;
583
584
585 -- *******************************************************
586 -- Standard check of p_simulate and p_commit parameters
587 -- *******************************************************
588
589 if (l_procedureEnabled) then
590 ecx_debug.pop(i_method_name);
591 end if;
592 EXCEPTION
593 WHEN g_exc_error THEN
594 ROLLBACK TO populate_value_extpt_PVT;
595 if (l_procedureEnabled) then
596 ecx_debug.pop(i_method_name);
597 end if;
598 p_return_status := g_ret_sts_error;
599 WHEN g_exc_unexpected_error THEN
600 ROLLBACK TO populate_value_extpt_PVT;
601 if (l_procedureEnabled) then
602 ecx_debug.pop(i_method_name);
603 end if;
604 p_return_status := g_ret_sts_error;
605 WHEN OTHERS THEN
606 if(l_unexpectedEnabled) then
607 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_CODE',i_method_name,'ERROR_CODE',SQLCODE);
608 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
609 end if;
610 ROLLBACK TO populate_value_extpt_PVT;
611 if (l_procedureEnabled) then
612 ecx_debug.pop(i_method_name);
613 end if;
614 p_return_status := g_ret_sts_error;
615 END convert_external_value;
616 /*
617 * This api does same as convert_external_value but will be
618 * called from java. Duplicate since to track properlogging from java
619 */
620 PROCEDURE convert_external_value_java(
621 i_standard_id IN NUMBER,
622 o_return_status OUT NOCOPY VARCHAR2,
623 io_value IN OUT NOCOPY VARCHAR2,
624 o_ext_code OUT NOCOPY VARCHAR2,
625 o_int_code OUT NOCOPY VARCHAR2,
626 i_category_id IN PLS_INTEGER,
627 i_snd_tp_id IN PLS_INTEGER,
628 i_rec_tp_id IN PLS_INTEGER)
629 IS
630 l_api_name CONSTANT VARCHAR2(30) := 'convert_external_value';
631 l_int_val VARCHAR2(4000) := NULL;
632 l_ext_val VARCHAR2(4000) := NULL;
633 l_var_value VARCHAR2(4000);
634 l_standard_id NUMBER;
635 l_univ_std_id pls_integer;
636 l_standard_type VARCHAR2(200);
637 BEGIN
638 -- Initialize API return status to success
639 o_return_status := g_ret_sts_success;
640 l_var_value := io_value;
641 BEGIN
642 SELECT standard_type
643 INTO l_standard_type
644 FROM ecx_standards
645 WHERE standard_id = i_standard_id;
646 EXCEPTION
647 WHEN OTHERS THEN
648 l_standard_type := 'XML';
649 END;
650 /* Bug 2110362
651 The Code conversion now has the following approach,
652 1. Obtain standard id meant for the standard CUSTOM.
653 2. Determine if code conversion is defined for CUSTOM.
654 If yes, return the value defined.
658 BEGIN
655 3. If No, determine if conversion is defined for the
656 transaction's standard (based on p_standard) and return
657 the value accordingly. */
659 SELECT standard_id
660 INTO l_standard_id
661 FROM ecx_standards
662 WHERE standard_code = 'CUSTOM'
663 AND standard_type = l_standard_type;
664 EXCEPTION
665 WHEN OTHERS THEN
666 l_standard_id := -1;
667 END;
668 IF io_value IS NOT NULL THEN
669 BEGIN
670 /* Bug 2110362
671 Replaced reference to i_standard_id with l_standard_id. */
672 SELECT xref_int_value
673 INTO l_int_val
674 FROM ecx_xref_dtl
675 WHERE tp_header_id = i_snd_tp_id
676 AND standard_id = l_standard_id
677 AND xref_category_id = i_category_id
678 AND xref_ext_value = io_value
679 AND direction = 'IN';
680 o_return_status := 0;
681 EXCEPTION
682 WHEN no_data_found THEN
683 BEGIN
684 SELECT xref_int_value
685 INTO l_int_val
686 FROM ecx_xref_standards
687 WHERE standard_id = i_standard_id
688 AND xref_category_id = i_category_id
689 AND xref_std_value = rtrim(io_value);
690 o_return_status := 0;
691 EXCEPTION
692 WHEN no_data_found THEN
693 BEGIN
694 SELECT standard_id
695 INTO l_univ_std_id
696 FROM ecx_standards
697 WHERE standard_code = 'UNIVERSAL'
698 AND standard_type = l_standard_type;
699 EXCEPTION
700 WHEN OTHERS THEN
701 l_univ_std_id := -1;
702 END;
703 BEGIN
704 SELECT xref_int_value
705 INTO l_int_val
706 FROM ecx_xref_standards
707 WHERE standard_id = l_univ_std_id
708 AND xref_category_id = i_category_id
709 AND xref_std_value = rtrim(io_value);
710 o_return_status := 0;
711 EXCEPTION
712 WHEN OTHERS THEN
713 o_return_status := ecx_code_conversion_PVT.g_xref_not_found;
714 io_value := l_var_value;
715 l_int_val := l_var_value;
716 END;
717 END;
718 END;
719 io_value := l_int_val;
720 o_int_code := l_int_val;
721 IF (l_int_val IS NOT NULL AND i_rec_tp_id IS NOT NULL) THEN
722 BEGIN
723 /* Bug 2110362
724 Replaced reference to i_standard_id with
725 l_standard_id. */
726 SELECT xref_ext_value
727 INTO l_ext_val
728 FROM ecx_xref_dtl
729 WHERE tp_header_id = i_rec_tp_id
730 AND standard_id = l_standard_id
731 AND xref_category_id = i_category_id
732 AND xref_int_value = l_int_val
733 AND direction = 'OUT';
734 o_return_status := 0;
735 EXCEPTION
736 WHEN no_data_found THEN
737 BEGIN
738 SELECT xref_std_value
739 INTO l_ext_val
740 FROM ecx_xref_standards
741 WHERE standard_id = i_standard_id
742 AND xref_category_id = i_category_id
743 AND xref_int_value = io_value;
744 o_return_status := 0;
745 EXCEPTION
746 WHEN no_data_found THEN
747 BEGIN
748 SELECT xref_std_value
749 INTO l_ext_val
750 FROM ecx_xref_standards
751 WHERE standard_id = l_univ_std_id
752 AND xref_category_id = i_category_id
753 AND xref_int_value = io_value;
754 o_return_status := 0;
755 EXCEPTION
756 WHEN OTHERS THEN
757 o_return_status := ecx_code_conversion_PVT.g_recv_xref_not_found;
758 RETURN;
759 END;
760 END;
761 END;
762 io_value := l_ext_val;
763 o_ext_code := l_ext_val;
764 END IF;
765 END IF;
766 EXCEPTION
767 WHEN g_exc_error THEN
768 o_return_status := g_ret_sts_error;
769 WHEN g_exc_unexpected_error THEN
770 o_return_status := g_ret_sts_error;
771 WHEN OTHERS THEN
772 o_return_status := g_ret_sts_error;
773 END convert_external_value_java;
774 END ecx_code_conversion_PVT;
[Home] [Help]