[Home] [Help]
20: i_c_by pls_integer := 0;
21: i_rowid varchar2(240) := null;
22:
23: begin
24: x_return_status := ECX_UTIL_API.G_NO_ERROR;
25: x_msg := null;
26: x_xref_hdr_id := -1;
27:
28: -- make sure xref_category_code is not null.
26: x_xref_hdr_id := -1;
27:
28: -- make sure xref_category_code is not null.
29: if (p_xref_category_code is null) then
30: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
31: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
32: return;
33: end if;
34:
58: if c_xref_hdr_id%ISOPEN then
59: CLOSE c_xref_hdr_id;
60: end if;
61: x_xref_hdr_id := -1;
62: x_return_status := ECX_UTIL_API.G_DUP_ERROR;
63: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_EXISTS',
64: 'p_xref_category_code', p_xref_category_code);
65: when others then
66: if c_xref_hdr_id%ISOPEN then
66: if c_xref_hdr_id%ISOPEN then
67: CLOSE c_xref_hdr_id;
68: end if;
69: x_xref_hdr_id := -1;
70: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
71: x_msg := SQLERRM;
72: end;
73:
74:
92: and exd.xref_category_id = p_xref_category_id;
93:
94: begin
95:
96: x_return_status := ECX_UTIL_API.G_NO_ERROR;
97: x_msg := null;
98:
99: if (p_xref_category_id is null) then
100: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
96: x_return_status := ECX_UTIL_API.G_NO_ERROR;
97: x_msg := null;
98:
99: if (p_xref_category_id is null) then
100: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
101: x_msg := ecx_debug.getTranslatedMessage('ECX_CATEGORY_ID_NOT_NULL');
102: return;
103: end if;
104:
107: fetch c into i_num;
108: close c;
109:
110: if (i_num = 1) then
111: x_return_status := ECX_UTIL_API.G_REFER_ERROR;
112: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_REFERENCED',
113: 'p_category_id', p_xref_category_id);
114: return;
115: end if;
120: close get_refrences;
121:
122: if (i_num > 0)
123: then
124: x_return_status := ECX_UTIL_API.G_REFER_ERROR;
125: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_REFERENCED',
126: 'p_category_id', p_xref_category_id);
127: return;
128: end if;
132: );
133:
134: exception
135: when no_data_found then
136: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
137: x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
138: 'p_table', 'ecx_xref_hdr',
139: 'p_param_name', 'Category Code ID',
140: 'p_param_id', p_xref_category_id);
145:
146: if c%ISOPEN then
147: CLOSE c;
148: end if;
149: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
150: x_msg := SQLERRM;
151: end;
152:
153:
162:
163: i_u_by pls_integer := 0;
164:
165: begin
166: x_return_status := ECX_UTIL_API.G_NO_ERROR;
167: x_msg := null;
168:
169: -- make sure xref_category_code is not null.
170: if (p_xref_category_code is null) then
167: x_msg := null;
168:
169: -- make sure xref_category_code is not null.
170: if (p_xref_category_code is null) then
171: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
172: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
173: return;
174: elsif (p_xref_category_id is null) then
175: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
171: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
172: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
173: return;
174: elsif (p_xref_category_id is null) then
175: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
176: x_msg := ecx_debug.getTranslatedMessage('ECX_CATEGORY_ID_NOT_NULL');
177: return;
178: end if;
179:
193: );
194:
195: exception
196: when no_data_found then
197: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
198: x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
199: 'p_table', 'ecx_xref_hdr_tl',
200: 'p_param_name', 'Cateogry Code ID',
201: 'p_param_id', p_xref_category_id);
200: 'p_param_name', 'Cateogry Code ID',
201: 'p_param_id', p_xref_category_id);
202:
203: when dup_val_on_index then
204: x_return_status := ECX_UTIL_API.G_DUP_ERROR;
205: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_EXISTS',
206: 'p_xref_category_code', p_xref_category_code);
207: when others then
208: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
204: x_return_status := ECX_UTIL_API.G_DUP_ERROR;
205: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_EXISTS',
206: 'p_xref_category_code', p_xref_category_code);
207: when others then
208: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
209: x_msg := SQLERRM;
210: end;
211:
212: ----------------------------------+
224: p_standard_type IN VARCHAR2
225: ) IS
226: begin
227:
228: x_return_status := ECX_UTIL_API.G_NO_ERROR;
229: x_msg := null;
230:
231: x_xref_dtl_id := -1;
232: x_xref_ext_value := null;
231: x_xref_dtl_id := -1;
232: x_xref_ext_value := null;
233:
234: if (p_xref_category_code is null) then
235: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
236: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
237: return;
238: elsif p_standard is null then
239: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
235: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
236: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
237: return;
238: elsif p_standard is null then
239: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
240: x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
241: return;
242: elsif (p_tp_header_id is null) then
243: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
239: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
240: x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
241: return;
242: elsif (p_tp_header_id is null) then
243: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
244: x_msg := ecx_debug.getTranslatedMessage('ECX_TP_HDR_ID_NOT_NULL');
245: return;
246: elsif (p_xref_int_value is null) then
247: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
243: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
244: x_msg := ecx_debug.getTranslatedMessage('ECX_TP_HDR_ID_NOT_NULL');
245: return;
246: elsif (p_xref_int_value is null) then
247: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
248: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_INT_VAL_NOT_NULL');
249: return;
250: end if;
251:
265: and exd.tp_header_id = p_tp_header_id
266: and es.standard_type = nvl(p_standard_type, 'XML');
267: exception
268: when too_many_rows then
269: x_return_status := ECX_UTIL_API.G_TOO_MANY_ROWS;
270: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_DTL_INT_TOO_MANY_ROWS',
271: 'p_standard_code', p_standard,
272: 'p_xref_category_code', p_xref_category_code,
273: 'p_tp_header_id', p_tp_header_id,
272: 'p_xref_category_code', p_xref_category_code,
273: 'p_tp_header_id', p_tp_header_id,
274: 'p_xref_int_value', p_xref_int_value);
275: when no_data_found then
276: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
277: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_DTL_INT_ID_NOT_FOUND',
278: 'p_standard_code', p_standard,
279: 'p_xref_category_code', p_xref_category_code,
280: 'p_tp_header_id', p_tp_header_id,
279: 'p_xref_category_code', p_xref_category_code,
280: 'p_tp_header_id', p_tp_header_id,
281: 'p_xref_int_value', p_xref_int_value);
282: when others then
283: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
284: x_msg := SQLERRM;
285: end;
286:
287:
297: p_standard_type IN VARCHAR2
298: ) IS
299: begin
300:
301: x_return_status := ECX_UTIL_API.G_NO_ERROR;
302: x_msg := null;
303: x_xref_int_value := null;
304: x_xref_dtl_id := -1;
305:
303: x_xref_int_value := null;
304: x_xref_dtl_id := -1;
305:
306: if (p_xref_category_code is null) then
307: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
308: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
309: return;
310: elsif p_standard is null then
311: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
307: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
308: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
309: return;
310: elsif p_standard is null then
311: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
312: x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
313: return;
314: elsif (p_tp_header_id is null) then
315: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
311: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
312: x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
313: return;
314: elsif (p_tp_header_id is null) then
315: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
316: x_msg := ecx_debug.getTranslatedMessage('ECX_TP_HDR_ID_NOT_NULL');
317: return;
318: elsif (p_xref_ext_value is null) then
319: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
315: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
316: x_msg := ecx_debug.getTranslatedMessage('ECX_TP_HDR_ID_NOT_NULL');
317: return;
318: elsif (p_xref_ext_value is null) then
319: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
320: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_EXT_VAL_NOT_NULL');
321: return;
322: end if;
323:
337: and exd.tp_header_id = p_tp_header_id
338: and es.standard_type = nvl(p_standard_type, 'XML');
339: exception
340: when too_many_rows then
341: x_return_status := ECX_UTIL_API.G_TOO_MANY_ROWS;
342: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_DTL_EXT_TOO_MANY_ROWS',
343: 'p_standard_code', p_standard,
344: 'p_xref_category_code', p_xref_category_code,
345: 'p_tp_header_id', p_tp_header_id,
344: 'p_xref_category_code', p_xref_category_code,
345: 'p_tp_header_id', p_tp_header_id,
346: 'p_xref_ext_value', p_xref_ext_value);
347: when no_data_found then
348: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
349: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_DTL_EXT_ID_NOT_FOUND',
350: 'p_standard_code', p_standard,
351: 'p_xref_category_code', p_xref_category_code,
352: 'p_tp_header_id', p_tp_header_id,
351: 'p_xref_category_code', p_xref_category_code,
352: 'p_tp_header_id', p_tp_header_id,
353: 'p_xref_ext_value', p_xref_ext_value);
354: when others then
355: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
356: x_msg := SQLERRM;
357: end;
358:
359:
377: x_last_update_date OUT NOCOPY DATE
378: ) is
379: begin
380:
381: x_return_status := ECX_UTIL_API.G_NO_ERROR;
382: x_msg := null;
383:
384:
385: if (p_xref_dtl_id is null) then
382: x_msg := null;
383:
384:
385: if (p_xref_dtl_id is null) then
386: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
387: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_DTL_ID_NOT_NULL');
388: return;
389: end if;
390:
425: and exd.xref_dtl_id = p_xref_dtl_id
426: and es.standard_id = exd.standard_id;
427: exception
428: when too_many_rows then
429: x_return_status := ECX_UTIL_API.G_TOO_MANY_ROWS;
430: x_msg := ecx_debug.getTranslatedMessage('ECX_TOO_MANY_ROWS',
431: 'p_table', 'ECX_XREF_DTL',
432: 'p_key', p_xref_dtl_id);
433:
431: 'p_table', 'ECX_XREF_DTL',
432: 'p_key', p_xref_dtl_id);
433:
434: when no_data_found then
435: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
436: x_msg := ecx_debug.getTranslatedMessage('ECX_NOT_FOUND',
437: 'p_table', 'ECX_XREF_DTL',
438: 'p_key', p_xref_dtl_id);
439: when others then
436: x_msg := ecx_debug.getTranslatedMessage('ECX_NOT_FOUND',
437: 'p_table', 'ECX_XREF_DTL',
438: 'p_key', p_xref_dtl_id);
439: when others then
440: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
441: x_msg := SQLERRM;
442: end;
443:
444:
463: p_standard_type IN VARCHAR2
464: ) is
465: begin
466:
467: x_return_status := ECX_UTIL_API.G_NO_ERROR;
468: x_msg := null;
469:
470: x_xref_dtl_id := -1;
471:
469:
470: x_xref_dtl_id := -1;
471:
472: if (p_xref_category_code is null) then
473: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
474: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
475: return;
476: elsif (p_standard is null) then
477: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
473: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
474: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
475: return;
476: elsif (p_standard is null) then
477: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
478: x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
479: return;
480: elsif (p_xref_ext_value is null) then
481: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
477: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
478: x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
479: return;
480: elsif (p_xref_ext_value is null) then
481: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
482: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_EXT_VAL_NOT_NULL');
483: return;
484: elsif (p_xref_int_value is null) then
485: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
481: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
482: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_EXT_VAL_NOT_NULL');
483: return;
484: elsif (p_xref_int_value is null) then
485: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
486: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_INT_VAL_NOT_NULL');
487: return;
488: elsif (p_direction is null) then
489: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
485: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
486: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_INT_VAL_NOT_NULL');
487: return;
488: elsif (p_direction is null) then
489: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
490: x_msg := ecx_debug.getTranslatedMessage('ECX_DIRECTION_NOT_NULL');
491: return;
492: end if;
493:
490: x_msg := ecx_debug.getTranslatedMessage('ECX_DIRECTION_NOT_NULL');
491: return;
492: end if;
493:
494: if NOT (ecx_util_api.validate_direction(p_direction)) then
495: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
496: x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_DIRECTION',
497: 'p_direction', p_direction);
498: return;
491: return;
492: end if;
493:
494: if NOT (ecx_util_api.validate_direction(p_direction)) then
495: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
496: x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_DIRECTION',
497: 'p_direction', p_direction);
498: return;
499: end if;
533: and es.standard_code = p_standard
534: and es.standard_type = nvl(p_standard_type, 'XML');
535: exception
536: when no_data_found then
537: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
538: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_DTL_NOT_FOUND',
539: 'p_standard_code', p_standard,
540: 'p_xref_category_code', p_xref_category_code,
541: 'p_direction' , p_direction,
542: 'p_xref_ext_value', p_xref_ext_value,
543: 'p_xref_int_value', p_xref_int_value,
544: 'p_standard_type', p_standard_type);
545: when others then
546: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
547: x_msg := SQLERRM;
548: end;
549:
550:
568: i_rowid varchar2(200) := null;
569:
570: begin
571:
572: x_return_status := ECX_UTIL_API.G_NO_ERROR;
573: x_msg := null;
574:
575: x_xref_dtl_id := -1;
576: x_xref_category_id := -1;
575: x_xref_dtl_id := -1;
576: x_xref_category_id := -1;
577:
578: if (p_xref_category_code is null) then
579: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
580: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
581: return;
582: elsif (p_standard is null) then
583: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
579: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
580: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
581: return;
582: elsif (p_standard is null) then
583: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
584: x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
585: return;
586: elsif (p_xref_ext_value is null) then
587: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
583: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
584: x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
585: return;
586: elsif (p_xref_ext_value is null) then
587: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
588: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_EXT_VAL_NOT_NULL');
589: return;
590: elsif (p_xref_int_value is null) then
591: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
587: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
588: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_EXT_VAL_NOT_NULL');
589: return;
590: elsif (p_xref_int_value is null) then
591: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
592: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_INT_VAL_NOT_NULL');
593: return;
594: elsif (p_tp_header_id is null) then
595: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
591: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
592: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_INT_VAL_NOT_NULL');
593: return;
594: elsif (p_tp_header_id is null) then
595: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
596: x_msg := ecx_debug.getTranslatedMessage('ECX_TP_HDR_ID_NOT_NULL');
597: return;
598: elsif (p_direction is null) then
599: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
595: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
596: x_msg := ecx_debug.getTranslatedMessage('ECX_TP_HDR_ID_NOT_NULL');
597: return;
598: elsif (p_direction is null) then
599: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
600: x_msg := ecx_debug.getTranslatedMessage('ECX_DIRECTION_NOT_NULL');
601: return;
602: end if;
603:
600: x_msg := ecx_debug.getTranslatedMessage('ECX_DIRECTION_NOT_NULL');
601: return;
602: end if;
603:
604: if NOT (ecx_util_api.validate_direction(p_direction)) then
605: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
606: x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_DIRECTION',
607: 'p_direction', p_direction);
608: return;
601: return;
602: end if;
603:
604: if NOT (ecx_util_api.validate_direction(p_direction)) then
605: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
606: x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_DIRECTION',
607: 'p_direction', p_direction);
608: return;
609: end if;
607: 'p_direction', p_direction);
608: return;
609: end if;
610:
611: if NOT (ecx_util_api.validate_trading_partner(p_tp_header_id))
612: then
613: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
614: x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_TP_HDR_ID',
615: 'p_tp_header_id', p_tp_header_id);
609: end if;
610:
611: if NOT (ecx_util_api.validate_trading_partner(p_tp_header_id))
612: then
613: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
614: x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_TP_HDR_ID',
615: 'p_tp_header_id', p_tp_header_id);
616: return;
617: end if;
622: from ecx_xref_hdr
623: where xref_category_code = p_xref_category_code;
624: exception
625: when no_data_found then
626: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
627: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_FOUND',
628: 'p_xref_category_code', p_xref_category_code);
629: return;
630: end;
657: exception
658: when no_data_found then
659: x_xref_dtl_id := -1;
660: x_xref_category_id := -1;
661: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
662: x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_FOUND',
663: 'p_standard', p_standard,
664: 'p_std_type', p_standard_type);
665: when dup_val_on_index then
664: 'p_std_type', p_standard_type);
665: when dup_val_on_index then
666: x_xref_dtl_id := -1;
667: x_xref_category_id := -1;
668: x_return_status := ECX_UTIL_API.G_DUP_ERROR;
669: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_DTL_ROW_EXISTS',
670: 'p_xref_category_code', p_xref_category_code,
671: 'p_standard_code', p_standard,
672: 'p_xref_ext_value',p_xref_ext_value,
674: 'p_direction', p_direction);
675: when others then
676: x_xref_dtl_id := -1;
677: x_xref_category_id := -1;
678: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
679: x_msg := SQLERRM;
680: end;
681:
682:
708: from ecx_xref_dtl
709: where xref_dtl_id = p_xref_dtl_id;
710:
711: begin
712: x_return_status := ECX_UTIL_API.G_NO_ERROR;
713: x_msg := null;
714:
715: if (p_xref_dtl_id is null) then
716: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
712: x_return_status := ECX_UTIL_API.G_NO_ERROR;
713: x_msg := null;
714:
715: if (p_xref_dtl_id is null) then
716: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
717: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_DTL_ID_NOT_NULL');
718: return;
719: elsif (p_xref_ext_value is null) then
720: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
716: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
717: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_DTL_ID_NOT_NULL');
718: return;
719: elsif (p_xref_ext_value is null) then
720: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
721: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_EXT_VAL_NOT_NULL');
722: return;
723: elsif (p_xref_int_value is null) then
724: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
720: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
721: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_EXT_VAL_NOT_NULL');
722: return;
723: elsif (p_xref_int_value is null) then
724: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
725: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_INT_VAL_NOT_NULL');
726: return;
727: elsif (p_tp_header_id is null) then
728: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
724: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
725: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_INT_VAL_NOT_NULL');
726: return;
727: elsif (p_tp_header_id is null) then
728: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
729: x_msg := ecx_debug.getTranslatedMessage('ECX_TP_HDR_ID_NOT_NULL');
730: return;
731: elsif (p_direction is null) then
732: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
728: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
729: x_msg := ecx_debug.getTranslatedMessage('ECX_TP_HDR_ID_NOT_NULL');
730: return;
731: elsif (p_direction is null) then
732: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
733: x_msg := ecx_debug.getTranslatedMessage('ECX_DIRECTION_NOT_NULL');
734: return;
735: end if;
736:
733: x_msg := ecx_debug.getTranslatedMessage('ECX_DIRECTION_NOT_NULL');
734: return;
735: end if;
736:
737: if NOT(ecx_util_api.validate_direction(p_direction)) then
738: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
739: x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_DIRECTION',
740: 'p_direction', p_direction);
741: return;
734: return;
735: end if;
736:
737: if NOT(ecx_util_api.validate_direction(p_direction)) then
738: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
739: x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_DIRECTION',
740: 'p_direction', p_direction);
741: return;
742: end if;
740: 'p_direction', p_direction);
741: return;
742: end if;
743:
744: if NOT (ecx_util_api.validate_trading_partner(p_tp_header_id))
745: then
746: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
747: x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_TP_HDR_ID',
748: 'p_tp_header_id', p_tp_header_id);
742: end if;
743:
744: if NOT (ecx_util_api.validate_trading_partner(p_tp_header_id))
745: then
746: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
747: x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_TP_HDR_ID',
748: 'p_tp_header_id', p_tp_header_id);
749: return;
750: end if;
768: LAST_UPDATED_BY = 0
769: where XREF_DTL_ID = p_xref_dtl_id;
770:
771: if (sql%rowcount = 0) then
772: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
773: x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
774: 'p_table', 'ecx_xref_dtl',
775: 'p_param_name', 'Xref detail ID',
776: 'p_param_id', p_xref_dtl_id);
784: where XREF_DTL_ID = p_xref_dtl_id
785: and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
786:
787: if (sql%rowcount = 0) then
788: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
789: x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
790: 'p_table', 'ecx_xref_dtl',
791: 'p_param_name', 'Xref detail ID',
792: 'p_param_id', p_xref_dtl_id);
798: then
799: CLOSE get_xref_dtl_data;
800: end if;
801:
802: x_return_status := ECX_UTIL_API.G_DUP_ERROR;
803: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_DTL_ID_ROW_EXISTS',
804: 'p_tp_header_id', p_tp_header_id,
805: 'p_xref_ext_value', p_xref_ext_value,
806: 'p_xref_int_value', p_xref_int_value,
810: then
811: CLOSE get_xref_dtl_data;
812: end if;
813:
814: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
815: x_msg := SQLERRM;
816: end;
817:
818:
822: p_xref_dtl_id IN PLS_INTEGER
823: ) is
824:
825: begin
826: x_return_status := ECX_UTIL_API.G_NO_ERROR;
827: x_msg := null;
828:
829: if (p_xref_dtl_id is null) then
830: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
826: x_return_status := ECX_UTIL_API.G_NO_ERROR;
827: x_msg := null;
828:
829: if (p_xref_dtl_id is null) then
830: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
831: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_DTL_ID_NOT_NULL');
832: return;
833: end if;
834:
836: X_XREF_DTL_ID => p_xref_dtl_id
837: );
838: exception
839: when no_data_found then
840: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
841: x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
842: 'p_table', 'ecx_xref_dtl',
843: 'p_param_name', 'Xref detail ID',
844: 'p_param_id', p_xref_dtl_id);
842: 'p_table', 'ecx_xref_dtl',
843: 'p_param_name', 'Xref detail ID',
844: 'p_param_id', p_xref_dtl_id);
845: when others then
846: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
847: x_msg := SQLERRM;
848: end;
849:
850: --------------------------------------+
870: p_standard_type IN VARCHAR2
871: ) is
872:
873: begin
874: x_return_status := ECX_UTIL_API.G_NO_ERROR;
875: x_msg := null;
876:
877: if (p_xref_category_code is null) then
878: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
874: x_return_status := ECX_UTIL_API.G_NO_ERROR;
875: x_msg := null;
876:
877: if (p_xref_category_code is null) then
878: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
879: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
880: return;
881: elsif (p_standard is null) then
882: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
878: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
879: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
880: return;
881: elsif (p_standard is null) then
882: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
883: x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
884: return;
885: elsif (p_xref_std_value is null) then
886: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
882: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
883: x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
884: return;
885: elsif (p_xref_std_value is null) then
886: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
887: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_STD_VAL_NOT_NULL');
888: return;
889: elsif (p_xref_int_value is null) then
890: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
886: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
887: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_STD_VAL_NOT_NULL');
888: return;
889: elsif (p_xref_int_value is null) then
890: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
891: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_INT_VAL_NOT_NULL');
892: return;
893: end if;
894:
924: and es.standard_type = nvl(p_standard_type, 'XML');
925:
926: exception
927: when no_data_found then
928: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
929: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_STD_NOT_FOUND',
930: 'p_xref_category_code', p_xref_category_code,
931: 'p_xref_std_value', p_xref_std_value,
932: 'p_xref_int_value', p_xref_int_value,
932: 'p_xref_int_value', p_xref_int_value,
933: 'p_standard', p_standard,
934: 'p_std_type', p_standard_type);
935: when others then
936: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
937: x_msg := SQLERRM;
938: end;
939:
940:
957: i_c_by pls_integer :=0;
958: i_rowid varchar2(200) := null;
959:
960: begin
961: x_return_status := ECX_UTIL_API.G_NO_ERROR;
962: x_msg := null;
963:
964: if (p_xref_category_code is null) then
965: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
961: x_return_status := ECX_UTIL_API.G_NO_ERROR;
962: x_msg := null;
963:
964: if (p_xref_category_code is null) then
965: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
966: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
967: return;
968: elsif (p_standard is null) then
969: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
965: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
966: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_NULL');
967: return;
968: elsif (p_standard is null) then
969: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
970: x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
971: return;
972: elsif (p_xref_std_value is null) then
973: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
969: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
970: x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
971: return;
972: elsif (p_xref_std_value is null) then
973: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
974: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_STD_VAL_NOT_NULL');
975: return;
976: elsif (p_xref_int_value is null) then
977: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
973: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
974: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_STD_VAL_NOT_NULL');
975: return;
976: elsif (p_xref_int_value is null) then
977: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
978: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_INT_VAL_NOT_NULL');
979: return;
980: elsif (p_data_seeded is null) then
981: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
977: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
978: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_INT_VAL_NOT_NULL');
979: return;
980: elsif (p_data_seeded is null) then
981: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
982: x_msg := ecx_debug.getTranslatedMessage('ECX_DATA_SEEDED_NOT_NULL');
983: return;
984: end if;
985:
983: return;
984: end if;
985:
986: -- validate data seeded flag
987: If NOT ecx_util_api.validate_data_seeded_flag(p_data_seeded)
988: then
989: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
990: x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_DATA_SEEDED',
991: 'p_data_seeded', p_data_seeded);
985:
986: -- validate data seeded flag
987: If NOT ecx_util_api.validate_data_seeded_flag(p_data_seeded)
988: then
989: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
990: x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_DATA_SEEDED',
991: 'p_data_seeded', p_data_seeded);
992: return;
993: end If;
999: where xref_category_code = p_xref_category_code;
1000:
1001: exception
1002: when no_data_found then
1003: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
1004: x_msg := ecx_debug.getTranslatedMessage('ECX_CODE_CATEGORY_NOT_FOUND',
1005: 'p_xref_category_code', p_xref_category_code);
1006: return;
1007: end;
1014: and standard_type = nvl(p_standard_type, 'XML');
1015: exception
1016: when no_data_found then
1017: x_xref_category_id := -1;
1018: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
1019: x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_FOUND',
1020: 'p_standard', p_standard,
1021: 'p_std_type', p_standard_type);
1022: return;
1027: if p_data_seeded = 'Y'
1028: then
1029: i_c_by :=1;
1030: else
1031: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1032: x_msg := ecx_debug.getTranslatedMessage('ECX_DATA_OWNER_INCONSISTENT',
1033: 'p_data_seeded', p_data_seeded,
1034: 'p_owner', p_owner);
1035: return;
1039: if NOT (p_data_seeded = 'Y')
1040: then
1041: i_c_by :=0;
1042: else
1043: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1044: x_msg := ecx_debug.getTranslatedMessage('ECX_DATA_OWNER_INCONSISTENT',
1045: 'p_data_seeded', p_data_seeded,
1046: 'p_owner', p_owner);
1047:
1047:
1048: return;
1049: end if;
1050: else
1051: x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1052: x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_OWNER',
1053: 'p_owner', p_owner);
1054: return;
1055: end if;
1077: exception
1078: when dup_val_on_index then
1079: x_xref_category_id := -1;
1080: x_xref_std_id := -1;
1081: x_return_status := ECX_UTIL_API.G_DUP_ERROR;
1082: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_STD_ROW_EXISTS',
1083: 'p_standard', p_standard,
1084: 'p_xref_std_value',p_xref_std_value,
1085: 'p_xref_int_value', p_xref_int_value);
1085: 'p_xref_int_value', p_xref_int_value);
1086: when others then
1087: x_xref_category_id := -1;
1088: x_xref_std_id := -1;
1089: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
1090: x_msg := SQLERRM;
1091: end;
1092:
1093:
1106: l_xref_std_value ecx_xref_standards.xref_std_value%type;
1107: l_xref_int_value ecx_xref_standards.xref_int_value%type;
1108:
1109: begin
1110: x_return_status := ECX_UTIL_API.G_NO_ERROR;
1111: x_msg := null;
1112:
1113: if (p_xref_standard_id is null) then
1114: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1110: x_return_status := ECX_UTIL_API.G_NO_ERROR;
1111: x_msg := null;
1112:
1113: if (p_xref_standard_id is null) then
1114: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1115: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_STANDARD_ID_NOT_NULL');
1116: return;
1117: elsif (p_xref_std_value is null) then
1118: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1114: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1115: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_STANDARD_ID_NOT_NULL');
1116: return;
1117: elsif (p_xref_std_value is null) then
1118: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1119: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_STD_VAL_NOT_NULL');
1120: return;
1121: elsif (p_xref_int_value is null) then
1122: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1118: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1119: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_STD_VAL_NOT_NULL');
1120: return;
1121: elsif (p_xref_int_value is null) then
1122: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1123: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_INT_VAL_NOT_NULL');
1124: return;
1125: end if;
1126:
1138: LAST_UPDATE_LOGIN = i_u_by
1139: where XREF_STANDARD_ID = p_xref_standard_id;
1140:
1141: if (sql%rowcount = 0) then
1142: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
1143: x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
1144: 'p_table', 'ecx_xref_standards',
1145: 'p_param_name', 'Xref standard ID',
1146: 'p_param_id', p_xref_standard_id);
1154: where XREF_STANDARD_ID = p_xref_standard_id
1155: and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1156:
1157: if (sql%rowcount = 0) then
1158: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
1159: x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
1160: 'p_table', 'ecx_xref_standards',
1161: 'p_param_name', 'Xref standard ID',
1162: 'p_param_id', p_xref_standard_id);
1163: return;
1164: end if;
1165: exception
1166: when dup_val_on_index then
1167: x_return_status := ECX_UTIL_API.G_DUP_ERROR;
1168: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_STD_ID_ROW_EXISTS',
1169: 'p_xref_standard_id', p_xref_standard_id,
1170: 'p_xref_std_value',p_xref_std_value,
1171: 'p_xref_int_value', p_xref_int_value);
1169: 'p_xref_standard_id', p_xref_standard_id,
1170: 'p_xref_std_value',p_xref_std_value,
1171: 'p_xref_int_value', p_xref_int_value);
1172: when others then
1173: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
1174: x_msg := SQLERRM;
1175: end;
1176:
1177:
1181: p_xref_standard_id IN PLS_INTEGER
1182: ) is
1183:
1184: begin
1185: x_return_status := ECX_UTIL_API.G_NO_ERROR;
1186: x_msg := null;
1187:
1188: if (p_xref_standard_id is null) then
1189: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1185: x_return_status := ECX_UTIL_API.G_NO_ERROR;
1186: x_msg := null;
1187:
1188: if (p_xref_standard_id is null) then
1189: x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1190: x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_STANDARD_ID_NOT_NULL');
1191: return;
1192: end if;
1193:
1196: );
1197:
1198: exception
1199: when no_data_found then
1200: x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
1201: x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
1202: 'p_table', 'ecx_xref_standards',
1203: 'p_param_name', 'Xref standard ID',
1204: 'p_param_id', p_xref_standard_id);
1202: 'p_table', 'ecx_xref_standards',
1203: 'p_param_name', 'Xref standard ID',
1204: 'p_param_id', p_xref_standard_id);
1205: when others then
1206: x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
1207: x_msg := SQLERRM;
1208: end;
1209:
1210: