[Home] [Help]
PACKAGE BODY: APPS.ECX_XREF_API
Source
1 package body ECX_XREF_API as
2 /* $Header: ECXXRFAB.pls 120.2 2005/06/30 11:19:07 appldev ship $ */
3
4 ----------------------------------+
5 -- XREFHDRAPI
6 ----------------------------------+
7 PROCEDURE create_code_category(
8 x_return_status OUT NOCOPY PLS_INTEGER,
9 x_msg OUT NOCOPY VARCHAR2,
10 x_xref_hdr_id OUT NOCOPY PLS_INTEGER,
11 p_xref_category_code IN VARCHAR2,
12 p_description IN VARCHAR2,
13 p_owner IN VARCHAR2
14 ) is
15
16 cursor c_xref_hdr_id
17 is select ecx_xref_hdr_s.nextval
18 from dual;
19
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.
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
35 if(p_owner = 'SEED') then
36 i_c_by := 1;
37 else
38 i_c_by := 0;
39 end if;
40
41 open c_xref_hdr_id;
42 fetch c_xref_hdr_id into x_xref_hdr_id;
43 close c_xref_hdr_id;
44
45 ECX_XREF_HDR_PKG.insert_row (
46 X_ROWID => i_rowid,
47 X_XREF_CATEGORY_CODE => p_xref_category_code,
48 X_XREF_CATEGORY_ID => x_xref_hdr_id,
49 X_DESCRIPTION => p_description,
50 X_CREATION_DATE => sysdate,
51 X_CREATED_BY => i_c_by,
52 X_LAST_UPDATE_DATE => sysdate,
53 X_LAST_UPDATED_BY => i_c_by,
54 X_LAST_UPDATE_LOGIN => i_c_by
55 );
56 exception
57 when dup_val_on_index then
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
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
75 PROCEDURE delete_code_category(
76 x_return_status OUT NOCOPY PLS_INTEGER,
77 x_msg OUT NOCOPY VARCHAR2,
78 p_xref_category_id IN PLS_INTEGER
79 ) IS
80
81 i_num pls_integer := 0;
82 l_xref_cat_code varchar2(30);
83
84 cursor c is
85 select 1 from ecx_object_attributes
86 where xref_category_id = p_xref_category_id;
87
88 cursor get_refrences is
89 select count(*) from ecx_xref_standards exs,
90 ecx_xref_dtl exd
91 where exs.xref_category_id = p_xref_category_id
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;
101 x_msg := ecx_debug.getTranslatedMessage('ECX_CATEGORY_ID_NOT_NULL');
102 return;
103 end if;
104
105 -- make sure xref_category is not used in any maps.
106 open c;
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;
116
117 i_num := 0;
118 open get_refrences;
119 fetch get_refrences into i_num;
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;
129
130 ECX_XREF_HDR_PKG.delete_row (
131 X_XREF_CATEGORY_ID => p_xref_category_id
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);
141 when others then
142 if get_refrences%ISOPEN then
143 CLOSE get_refrences;
144 end if;
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
154 PROCEDURE update_code_category(
155 x_return_status OUT NOCOPY PLS_INTEGER,
156 x_msg OUT NOCOPY VARCHAR2,
157 p_xref_category_id IN PLS_INTEGER,
158 p_xref_category_code IN VARCHAR2,
159 p_description IN VARCHAR2,
160 p_owner IN VARCHAR2
161 ) is
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
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
180 if(p_owner = 'SEED') then
181 i_u_by := 1;
182 else
183 i_u_by := 0;
184 end if;
185
186 ECX_XREF_HDR_PKG.update_row (
187 X_XREF_CATEGORY_ID => p_xref_category_id,
188 X_XREF_CATEGORY_CODE => p_xref_category_code,
189 X_DESCRIPTION => p_description,
190 X_LAST_UPDATE_DATE => sysdate,
191 X_LAST_UPDATED_BY => i_u_by,
192 X_LAST_UPDATE_LOGIN => i_u_by
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);
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;
209 x_msg := SQLERRM;
210 end;
211
212 ----------------------------------+
213 -- XREFDTLAPI
214 ----------------------------------+
215 PROCEDURE retrieve_tp_external_value(
216 x_return_status OUT NOCOPY PLS_INTEGER,
217 x_msg OUT NOCOPY VARCHAR2,
218 x_xref_ext_value OUT NOCOPY VARCHAR2,
219 p_tp_header_id IN PLS_INTEGER,
220 p_xref_category_code IN VARCHAR2,
221 p_standard IN VARCHAR2,
222 p_xref_int_value IN VARCHAR2,
223 x_xref_dtl_id OUT NOCOPY PLS_INTEGER,
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;
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;
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;
248 x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_INT_VAL_NOT_NULL');
249 return;
250 end if;
251
252 select xref_dtl_id,
253 exd.xref_ext_value
254 into x_xref_dtl_id,
255 x_xref_ext_value
256 from ECX_XREF_HDR exh,
257 ECX_XREF_DTL exd,
258 ECX_STANDARDS es
259 where exh.xref_category_code = p_xref_category_code
260 and exh.xref_category_id = exd.xref_category_id
261 and exd.direction = 'OUT'
262 and exd.xref_int_value = p_xref_int_value
263 and es.standard_id = exd.standard_id
264 and es.standard_code = p_standard
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,
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,
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
288 PROCEDURE retrieve_tp_internal_value(
289 x_return_status OUT NOCOPY PLS_INTEGER,
290 x_msg OUT NOCOPY VARCHAR2,
291 x_xref_int_value OUT NOCOPY VARCHAR2,
292 p_tp_header_id IN PLS_INTEGER,
293 p_xref_category_code IN VARCHAR2,
294 p_standard IN VARCHAR2,
295 p_xref_ext_value IN VARCHAR2,
296 x_xref_dtl_id OUT NOCOPY PLS_INTEGER,
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
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;
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;
320 x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_EXT_VAL_NOT_NULL');
321 return;
322 end if;
323
324 select xref_dtl_id,
325 exd.xref_int_value
326 into x_xref_dtl_id,
327 x_xref_int_value
328 from ECX_XREF_HDR exh,
329 ECX_XREF_DTL exd,
330 ECX_STANDARDS es
331 where exh.xref_category_code = p_xref_category_code
332 and exh.xref_category_id = exd.xref_category_id
333 and exd.direction = 'IN'
334 and exd.xref_ext_value = p_xref_ext_value
335 and es.standard_id = exd.standard_id
336 and es.standard_code = p_standard
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,
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,
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
360 PROCEDURE retrieve_tp_code_values_by_id(
361 x_return_status OUT NOCOPY PLS_INTEGER,
362 x_msg OUT NOCOPY VARCHAR2,
363 x_xref_category_code OUT NOCOPY VARCHAR2,
364 x_standard_code OUT NOCOPY VARCHAR2,
365 x_xref_ext_value OUT NOCOPY VARCHAR2,
366 x_xref_int_value OUT NOCOPY VARCHAR2,
367 x_direction OUT NOCOPY VARCHAR2,
368 p_xref_dtl_id IN PLS_INTEGER,
369 x_cat_description OUT NOCOPY VARCHAR2,
370 x_xref_category_id OUT NOCOPY NUMBER,
371 x_standard_id OUT NOCOPY PLS_INTEGER,
372 x_tp_header_id OUT NOCOPY PLS_INTEGER,
373 x_description OUT NOCOPY VARCHAR2,
374 x_created_by OUT NOCOPY PLS_INTEGER,
375 x_creation_date OUT NOCOPY DATE,
376 x_last_updated_by OUT NOCOPY PLS_INTEGER,
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
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
391 select
392 exh.xref_category_code,
393 es.standard_code,
394 exd.xref_ext_value,
395 exd.xref_int_value,
396 exd.direction,
397 exh.DESCRIPTION HDR_DESCRIPTION,
398 exd.XREF_CATEGORY_ID XREF_CATEGORY_ID,
399 es.STANDARD_ID,
400 TP_HEADER_ID,
401 exd.DESCRIPTION DTL_DESCRIPTION,
402 exd.LAST_UPDATE_DATE,
403 exd.LAST_UPDATED_BY,
404 exd.CREATION_DATE,
405 exd.CREATED_BY
406 into
407 x_xref_category_code,
408 x_standard_code,
409 x_xref_ext_value,
410 x_xref_int_value,
411 x_direction,
412 x_cat_description,
413 x_xref_category_id,
414 x_standard_id,
415 x_tp_header_id,
416 x_description,
417 x_last_update_date,
418 x_last_updated_by,
419 x_creation_date,
420 x_created_by
421 from ECX_XREF_HDR_VL exh,
422 ECX_XREF_DTL_VL exd,
423 ECX_STANDARDS es
424 where exh.xref_category_id = exd.xref_category_id
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
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
440 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
441 x_msg := SQLERRM;
442 end;
443
444
445 PROCEDURE retrieve_tp_code_values(
446 x_return_status OUT NOCOPY PLS_INTEGER,
447 x_msg OUT NOCOPY VARCHAR2,
448 x_xref_dtl_id OUT NOCOPY PLS_INTEGER,
449 x_xref_category_id OUT NOCOPY PLS_INTEGER,
450 p_xref_category_code IN VARCHAR2,
451 p_standard IN VARCHAR2,
452 p_xref_ext_value IN VARCHAR2,
453 p_xref_int_value IN VARCHAR2,
454 p_direction IN VARCHAR2,
455 x_cat_description OUT NOCOPY VARCHAR2,
456 x_standard_id OUT NOCOPY PLS_INTEGER,
457 x_tp_header_id OUT NOCOPY PLS_INTEGER,
458 x_description OUT NOCOPY VARCHAR2,
459 x_created_by OUT NOCOPY PLS_INTEGER,
460 x_creation_date OUT NOCOPY DATE,
461 x_last_updated_by OUT NOCOPY PLS_INTEGER,
462 x_last_update_date OUT NOCOPY DATE,
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
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;
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;
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
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;
500
501
502 select
503 exh.DESCRIPTION HDR_DESCRIPTION,
504 XREF_DTL_ID,
505 exd.XREF_CATEGORY_ID XREF_CATEGORY_ID,
506 es.STANDARD_ID,
507 TP_HEADER_ID,
508 exd.DESCRIPTION DTL_DESCRIPTION,
509 exd.LAST_UPDATE_DATE,
510 exd.LAST_UPDATED_BY,
511 exd.CREATION_DATE,
512 exd.CREATED_BY
513 into
514 x_cat_description,
515 x_xref_dtl_id,
516 x_xref_category_id,
517 x_standard_id,
518 x_tp_header_id,
519 x_description,
520 x_last_update_date,
521 x_last_updated_by,
522 x_creation_date,
523 x_created_by
524 from ECX_XREF_HDR_VL exh,
525 ECX_XREF_DTL_VL exd,
526 ECX_STANDARDS es
527 where exh.xref_category_code = p_xref_category_code
528 and exh.xref_category_id = exd.xref_category_id
529 and exd.direction = p_direction
530 and exd.xref_ext_value = p_xref_ext_value
531 and exd.xref_int_value = p_xref_int_value
532 and es.standard_id = exd.standard_id
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
551 PROCEDURE create_tp_code_values(
552 x_return_status OUT NOCOPY PLS_INTEGER,
553 x_msg OUT NOCOPY VARCHAR2,
554 x_xref_dtl_id OUT NOCOPY PLS_INTEGER,
555 x_xref_category_id OUT NOCOPY PLS_INTEGER,
556 p_xref_category_code IN VARCHAR2,
557 p_standard IN VARCHAR2,
558 p_tp_header_id IN PLS_INTEGER,
559 p_xref_ext_value IN VARCHAR2,
560 p_xref_int_value IN VARCHAR2,
561 p_description IN VARCHAR2,
562 p_direction IN VARCHAR2,
563 p_standard_type IN VARCHAR2
564
565 ) is
566
567 i_standard_id pls_integer := 0;
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;
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;
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;
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;
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;
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;
618
619 begin
620 select xref_category_id
621 into x_xref_category_id
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;
631
632 select standard_id
633 into i_standard_id
634 from ecx_standards
635 where standard_code = p_standard
636 and standard_type = nvl(p_standard_type, 'XML');
637
638 select ecx_xref_dtl_s.nextval into x_xref_dtl_id from dual;
639
640 ECX_XREF_DTL_PKG. insert_row (
641 X_ROWID => i_rowid,
642 X_XREF_DTL_ID => x_xref_dtl_id,
643 X_XREF_CATEGORY_ID => x_xref_category_id,
644 X_STANDARD_ID => i_standard_id,
645 X_XREF_STANDARD_CODE => p_standard,
646 X_TP_HEADER_ID => p_tp_header_id,
647 X_XREF_EXT_VALUE => p_xref_ext_value,
648 X_XREF_INT_VALUE => p_xref_int_value,
649 X_DIRECTION => p_direction,
650 X_DESCRIPTION => p_description,
651 X_CREATION_DATE => sysdate,
652 X_CREATED_BY => 0,
653 X_LAST_UPDATE_DATE => sysdate,
654 X_LAST_UPDATED_BY => 0,
655 X_LAST_UPDATE_LOGIN => 0
656 );
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
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,
673 'p_xref_int_value', p_xref_int_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
683 PROCEDURE update_tp_code_values(
684 x_return_status OUT NOCOPY PLS_INTEGER,
685 x_msg OUT NOCOPY VARCHAR2,
686 p_xref_dtl_id IN PLS_INTEGER,
687 p_xref_ext_value IN VARCHAR2,
688 p_xref_int_value IN VARCHAR2,
689 p_tp_header_id IN PLS_INTEGER,
690 p_description IN VARCHAR2,
691 p_direction IN VARCHAR2
692 ) is
693
694 l_xref_cat_id ecx_xref_hdr.xref_category_id%type;
695 l_standard_id ecx_xref_standards.standard_id%type;
696 l_tp_header_id ecx_xref_dtl.tp_header_id%type;
697 l_xref_int_value ecx_xref_dtl.xref_int_value%type;
698 l_xref_ext_value ecx_xref_dtl.xref_ext_value%type;
699 l_direction ecx_xref_dtl.direction%type;
700
701 cursor get_xref_dtl_data is
702 select xref_category_id,
703 standard_id,
704 tp_header_id,
705 xref_int_value,
706 xref_ext_value,
707 direction
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;
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;
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;
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;
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;
751
752 open get_xref_dtl_data;
753 fetch get_xref_dtl_data
754 into l_xref_cat_id,
755 l_standard_id,
756 l_tp_header_id,
757 l_xref_int_value,
758 l_xref_ext_value,
759 l_direction;
760 close get_xref_dtl_data;
761
762 update ECX_XREF_DTL set
763 XREF_EXT_VALUE = p_xref_ext_value,
764 XREF_INT_VALUE = p_xref_int_value,
765 TP_HEADER_ID = p_tp_header_id,
766 DIRECTION = p_direction,
767 LAST_UPDATE_DATE = sysdate,
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);
777 return;
778 end if;
779
780 -- update description for only the current language
781 update ECX_XREF_DTL_TL set
782 DESCRIPTION = p_description,
783 SOURCE_LANG = userenv('LANG')
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);
793 return;
794 end if;
795 exception
796 when dup_val_on_index then
797 if get_xref_dtl_data%ISOPEN
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,
807 'p_direction', p_direction);
808 when others then
809 if get_xref_dtl_data%ISOPEN
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
819 PROCEDURE delete_tp_code_values(
820 x_return_status OUT NOCOPY PLS_INTEGER,
821 x_msg OUT NOCOPY VARCHAR2,
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;
831 x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_DTL_ID_NOT_NULL');
832 return;
833 end if;
834
835 ECX_XREF_DTL_PKG.delete_row (
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);
845 when others then
846 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
847 x_msg := SQLERRM;
848 end;
849
850 --------------------------------------+
851 -- XREFSTDAPI
852 --------------------------------------+
853 PROCEDURE retrieve_standard_code_values(
854 x_return_status OUT NOCOPY PLS_INTEGER,
855 x_msg OUT NOCOPY VARCHAR2,
856 x_xref_std_id OUT NOCOPY PLS_INTEGER,
857 x_xref_category_id OUT NOCOPY PLS_INTEGER,
858 p_xref_category_code IN VARCHAR2,
859 p_standard IN VARCHAR2,
860 p_xref_std_value IN VARCHAR2,
861 p_xref_int_value IN VARCHAR2,
862 x_cat_description OUT NOCOPY VARCHAR2,
863 x_standard_id OUT NOCOPY PLS_INTEGER,
864 x_description OUT NOCOPY VARCHAR2,
865 x_data_seeded OUT NOCOPY VARCHAR2,
866 x_created_by OUT NOCOPY PLS_INTEGER,
867 x_creation_date OUT NOCOPY DATE,
868 x_last_updated_by OUT NOCOPY PLS_INTEGER,
869 x_last_update_date OUT NOCOPY DATE,
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;
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;
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
895 select exh.DESCRIPTION HDR_DESCRIPTION,
896 XREF_STANDARD_ID,
897 exs.XREF_CATEGORY_ID XREF_CATEGORY_ID,
898 exs.STANDARD_ID STANDARD_ID,
899 exs.DESCRIPTION DTL_DESCRIPTION,
900 exs.DATA_SEEDED,
901 exs.LAST_UPDATE_DATE,
902 exs.LAST_UPDATED_BY,
903 exs.CREATION_DATE,
904 exs.CREATED_BY
905 into x_cat_description,
906 x_xref_std_id,
907 x_xref_category_id,
908 x_standard_id,
909 x_description,
910 x_data_seeded,
911 x_last_update_date,
912 x_last_updated_by,
913 x_creation_date,
914 x_created_by
915 from ECX_XREF_HDR_vl exh,
916 ECX_XREF_STANDARDS_vl exs,
917 ECX_STANDARDS es
918 where exh.xref_category_code = p_xref_category_code
919 and exh.xref_category_id = exs.xref_category_id
920 and exs.xref_std_value = p_xref_std_value
921 and exs.xref_int_value = p_xref_int_value
922 and es.standard_id = exs.standard_id
923 and es.standard_code = p_standard
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,
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
941 PROCEDURE create_standard_code_values(
942 x_return_status OUT NOCOPY PLS_INTEGER,
943 x_msg OUT NOCOPY VARCHAR2,
944 x_xref_std_id OUT NOCOPY PLS_INTEGER,
945 x_xref_category_id OUT NOCOPY PLS_INTEGER,
946 p_xref_category_code IN VARCHAR2,
947 p_standard IN VARCHAR2,
948 p_xref_std_value IN VARCHAR2,
949 p_xref_int_value IN VARCHAR2,
950 p_description IN VARCHAR2,
951 p_data_seeded IN VARCHAR2,
952 p_owner IN VARCHAR2,
953 p_standard_type IN VARCHAR2
954 ) is
955
956 i_standard_id number :=0;
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;
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;
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;
982 x_msg := ecx_debug.getTranslatedMessage('ECX_DATA_SEEDED_NOT_NULL');
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);
992 return;
993 end If;
994
995 begin
996 select xref_category_id
997 into x_xref_category_id
998 from ecx_xref_hdr
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;
1008
1009 begin
1010 select standard_id
1011 into i_standard_id
1012 from ecx_standards
1013 where standard_code = p_standard
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;
1023 end;
1024
1025 if p_owner = 'SEED'
1026 then
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;
1036 end if;
1037 elsif p_owner = 'CUSTOM'
1038 then
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
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;
1056
1057 select ecx_xref_standards_s.nextval
1058 into x_xref_std_id from dual;
1059
1060 ECX_XREF_STANDARDS_PKG.insert_row (
1061 X_ROWID => i_rowid,
1062 X_XREF_STANDARD_ID => x_xref_std_id,
1063 X_XREF_CATEGORY_ID => x_xref_category_id,
1064 X_STANDARD_ID => i_standard_id,
1065 X_XREF_STANDARD_CODE => p_standard,
1066 X_XREF_STD_VALUE => p_xref_std_value,
1067 X_XREF_INT_VALUE => p_xref_int_value,
1068 X_DATA_SEEDED => p_data_seeded,
1069 X_DESCRIPTION => p_description,
1070 X_CREATION_DATE => sysdate,
1071 X_CREATED_BY => i_c_by,
1072 X_LAST_UPDATE_DATE => sysdate,
1073 X_LAST_UPDATED_BY => i_c_by,
1074 X_LAST_UPDATE_LOGIN => i_c_by
1075 );
1076
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);
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
1094 PROCEDURE update_standard_code_values(
1095 x_return_status OUT NOCOPY PLS_INTEGER,
1096 x_msg OUT NOCOPY VARCHAR2,
1097 p_xref_standard_id IN PLS_INTEGER,
1098 p_xref_std_value IN VARCHAR2,
1099 p_xref_int_value IN VARCHAR2,
1100 p_description IN VARCHAR2,
1101 p_owner IN VARCHAR2
1102 ) is
1103 i_u_by pls_integer := 0;
1104 l_xref_cat_id ecx_xref_hdr.xref_category_id%type;
1105 l_standard_id ecx_xref_standards.standard_id%type;
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;
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;
1123 x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_INT_VAL_NOT_NULL');
1124 return;
1125 end if;
1126
1127 if(p_owner = 'SEED') then
1128 i_u_by := 1;
1129 else
1130 i_u_by := 0;
1131 end if;
1132
1133 update ECX_XREF_STANDARDS set
1134 XREF_STD_VALUE = p_xref_std_value,
1135 XREF_INT_VALUE = p_xref_int_value,
1136 LAST_UPDATE_DATE = sysdate,
1137 LAST_UPDATED_BY = i_u_by,
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);
1147 return;
1148 end if;
1149
1150 -- update description for the current language
1151 update ECX_XREF_STANDARDS_TL set
1152 DESCRIPTION = p_description,
1153 SOURCE_LANG = userenv('LANG')
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);
1172 when others then
1173 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
1174 x_msg := SQLERRM;
1175 end;
1176
1177
1178 PROCEDURE delete_standard_code_values(
1179 x_return_status OUT NOCOPY PLS_INTEGER,
1180 x_msg OUT NOCOPY VARCHAR2,
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;
1190 x_msg := ecx_debug.getTranslatedMessage('ECX_XREF_STANDARD_ID_NOT_NULL');
1191 return;
1192 end if;
1193
1194 ECX_XREF_STANDARDS_PKG.delete_row (
1195 X_XREF_STANDARD_ID => p_xref_standard_id
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);
1205 when others then
1206 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
1207 x_msg := SQLERRM;
1208 end;
1209
1210
1211 end ECX_XREF_API;