[Home] [Help]
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,
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);
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:
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
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);
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;
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
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
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';
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;
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,
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
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
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;
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
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
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;
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;
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
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);
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
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);
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
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;
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
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;
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;
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
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);
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;
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,
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;
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
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:
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);
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);
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,
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);
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:
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
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);
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;
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
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
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';
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;
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,
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
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
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;
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
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
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;
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;
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
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
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;
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;
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
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;
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
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;
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;
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;
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
527:
528: IF (l_int_val IS NOT NULL AND p_rec_tp_id IS NOT NULL) THEN
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
528: IF (l_int_val IS NOT NULL AND p_rec_tp_id IS NOT NULL) THEN
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
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;
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;
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;
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
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);
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,
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
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;
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
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
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';
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;
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.
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.
655: 3. If No, determine if conversion is defined for the
656: transaction's standard (based on p_standard) and return
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.
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. */
658: BEGIN
659: SELECT standard_id
660: INTO l_standard_id
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. */
658: BEGIN
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;
656: transaction's standard (based on p_standard) and return
657: the value accordingly. */
658: BEGIN
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
657: the value accordingly. */
658: BEGIN
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
658: BEGIN
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;
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;
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
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
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;
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;
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
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;
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
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;
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;
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;
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
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
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
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;
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;
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;
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