1 package body ECX_XREF_DTL_PKG as
2 /* $Header: ECXXRFDB.pls 115.5 2003/11/14 17:44:59 mtai ship $ */
3
4 procedure TRANSLATE_ROW (
5 X_XREF_CATEGORY_ID IN NUMBER,
6 X_STANDARD_ID IN NUMBER,
7 X_TP_HEADER_ID IN NUMBER,
8 X_XREF_EXT_VALUE IN VARCHAR2,
9 X_XREF_INT_VALUE IN VARCHAR2,
10 X_DIRECTION IN VARCHAR2,
11 X_DESCRIPTION IN VARCHAR2,
12 X_OWNER IN VARCHAR2,
13 X_CUSTOM_MODE IN VARCHAR2)
14 is
15 l_luby number; -- entity owner in file
16 l_ludate date; -- entity update date in file
17 l_db_luby number; -- entity owner in db
18 l_db_ludate date; -- entity update date in db
19 l_dtl_id number;
20 begin
21
22 -- Translate owner to file_last_updated_by
23 if (x_owner = 'SEED') then
24 l_luby := 1;
25 else
26 l_luby := 0;
27 end if;
28
29 -- Translate char last_update_date to date
30 l_ludate := sysdate;
31
32 begin
33 select xref_dtl_id
34 into l_dtl_id
35 from ECX_XREF_DTL_B
36 where XREF_CATEGORY_ID = X_XREF_CATEGORY_ID
37 and standard_id = X_STANDARD_ID
38 and tp_header_id = X_TP_HEADER_ID
39 and xref_int_value = X_XREF_INT_VALUE
40 and xref_ext_value = X_XREF_EXT_VALUE
41 and direction = X_DIRECTION;
42
43 select LAST_UPDATED_BY, LAST_UPDATE_DATE
44 into l_db_luby, l_db_ludate
45 from ECX_XREF_DTL_TL
46 where XREF_DTL_ID = l_dtl_id
47 and LANGUAGE = userenv('LANG');
48
49 -- Update record, honoring customization mode.
50 -- Record should be updated only if:
51 -- a. CUSTOM_MODE = FORCE, or
52 -- b. file owner is CUSTOM, db owner is SEED
53 -- c. owners are the same, and file_date > db_date
54 if ((x_custom_mode = 'FORCE') or
55 ((l_luby = 0) and (l_db_luby = 1)) or
56 ((l_luby = l_db_luby) and (l_ludate > l_db_ludate)))
57 then
58 update ECX_XREF_DTL_TL set
59 DESCRIPTION = nvl(x_description, DESCRIPTION),
60 SOURCE_LANG = userenv('LANG'),
61 LAST_UPDATE_DATE = l_ludate,
62 LAST_UPDATED_BY = l_luby,
63 LAST_UPDATE_LOGIN = 0
64 where XREF_DTL_ID = l_dtl_id
65 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
66 end if;
67 exception
68 when no_data_found then
69 null;
70 end;
71 end TRANSLATE_ROW;
72
73 procedure LOAD_ROW (
74 X_XREF_CATEGORY_CODE IN VARCHAR2,
75 X_STANDARD_CODE IN VARCHAR2,
76 X_TP_HEADER_ID IN NUMBER,
77 X_XREF_EXT_VALUE IN VARCHAR2,
78 X_XREF_INT_VALUE IN VARCHAR2,
79 X_DIRECTION IN VARCHAR2,
80 X_DESCRIPTION IN VARCHAR2,
81 X_OWNER IN VARCHAR2,
82 X_CUSTOM_MODE IN VARCHAR2,
83 X_STANDARD_TYPE IN VARCHAR2)
84 is
85 l_luby number; -- entity owner in file
86 l_ludate date; -- entity update date in file
87 l_db_luby number; -- entity owner in db
88 l_db_ludate date; -- entity update date in db
89 l_ret_code pls_integer :=0;
90 l_errmsg varchar2(2000) := null;
91 l_dtl_id number := 0;
92 l_hdr_id number :=0;
93 l_standard_id number :=0;
94 begin
95 -- Translate owner to file_last_updated_by
96 if (x_owner = 'SEED') then
97 l_luby := 1;
98 else
99 l_luby := 0;
100 end if;
101
102 -- Translate char last_update_date to date
103 l_ludate := sysdate;
104
105 begin
106 select XREF_CATEGORY_ID
107 into l_hdr_id
108 from ecx_xref_hdr_b
109 where XREF_CATEGORY_CODE = X_XREF_CATEGORY_CODE;
110
111 select STANDARD_ID
112 into l_standard_id
113 from ecx_standards_b
114 where STANDARD_CODE = X_STANDARD_CODE
115 and STANDARD_TYPE = nvl(X_STANDARD_TYPE, 'XML');
116
117 select XREF_DTL_ID
118 into l_dtl_id
119 from ECX_XREF_DTL_B
120 where XREF_CATEGORY_ID = l_hdr_id
121 and standard_id = l_standard_id
122 and tp_header_id = X_TP_HEADER_ID
123 and xref_int_value = X_XREF_INT_VALUE
124 and xref_ext_value = X_XREF_EXT_VALUE
125 and direction = X_DIRECTION;
126
127 -- Update record, honoring customization mode.
128 -- Record should be updated only if:
129 -- a. CUSTOM_MODE = FORCE, or
130 -- b. file owner is CUSTOM, db owner is SEED
131 -- c. owners are the same, and file_date > db_date
132 if ((x_custom_mode = 'FORCE') or
133 ((l_luby = 0) and (l_db_luby = 1)) or
134 ((l_luby = l_db_luby) and (l_ludate > l_db_ludate)))
135 then
136 ecx_xref_api.update_tp_code_values(
137 x_return_status => l_ret_code,
138 x_msg => l_errmsg,
139 p_xref_dtl_id => l_dtl_id,
140 p_xref_ext_value => X_XREF_EXT_VALUE,
141 p_xref_int_value => X_XREF_INT_VALUE,
142 p_tp_header_id => X_TP_HEADER_ID,
143 p_description => X_DESCRIPTION,
144 p_direction => X_DIRECTION
145 );
146
147 if NOT(l_ret_code = ECX_UTIL_API.G_NO_ERROR) then
148 raise_application_error(-20000, l_errmsg);
149 end if;
150
151 end if;
152 exception
153 when no_data_found then
154 ecx_xref_api.create_tp_code_values(
155 x_return_status => l_ret_code,
156 x_msg => l_errmsg,
157 x_xref_dtl_id => l_dtl_id,
158 x_xref_category_id => l_hdr_id,
159 p_xref_category_code => X_XREF_CATEGORY_CODE,
160 p_standard => X_STANDARD_CODE,
161 p_tp_header_id => X_TP_HEADER_ID,
162 p_xref_ext_value => X_XREF_EXT_VALUE,
163 p_xref_int_value => X_XREF_INT_VALUE,
164 p_description => X_DESCRIPTION,
165 p_direction => X_DIRECTION,
166 p_standard_type => X_STANDARD_TYPE
167 );
168 if NOT(l_ret_code = ECX_UTIL_API.G_NO_ERROR) then
169 raise_application_error(-20000, l_errmsg);
170 end if;
171 when others then
172 raise;
173 end;
174 end LOAD_ROW;
175
176 procedure INSERT_ROW (
177 X_ROWID in out NOCOPY VARCHAR2,
178 X_XREF_DTL_ID in NUMBER,
179 X_XREF_CATEGORY_ID in NUMBER,
180 X_STANDARD_ID in NUMBER,
181 X_XREF_STANDARD_CODE in VARCHAR2,
182 X_TP_HEADER_ID in NUMBER,
183 X_XREF_EXT_VALUE in VARCHAR2,
184 X_XREF_INT_VALUE in VARCHAR2,
185 X_DIRECTION in VARCHAR2,
186 X_DESCRIPTION in VARCHAR2,
187 X_CREATION_DATE in DATE,
188 X_CREATED_BY in NUMBER,
189 X_LAST_UPDATE_DATE in DATE,
190 X_LAST_UPDATED_BY in NUMBER,
191 X_LAST_UPDATE_LOGIN in NUMBER
192 ) is
193 cursor C is select ROWID from ECX_XREF_DTL_B
194 where XREF_DTL_ID = X_XREF_DTL_ID
195 ;
196 begin
197 insert into ECX_XREF_DTL_B (
198 XREF_DTL_ID,
199 XREF_CATEGORY_ID,
200 STANDARD_ID,
201 XREF_STANDARD_CODE,
202 TP_HEADER_ID,
203 XREF_EXT_VALUE,
204 XREF_INT_VALUE,
205 DIRECTION,
206 CREATION_DATE,
207 CREATED_BY,
208 LAST_UPDATE_DATE,
209 LAST_UPDATED_BY,
210 LAST_UPDATE_LOGIN
211 ) values (
212 X_XREF_DTL_ID,
213 X_XREF_CATEGORY_ID,
214 X_STANDARD_ID,
215 X_XREF_STANDARD_CODE,
216 X_TP_HEADER_ID,
217 X_XREF_EXT_VALUE,
218 X_XREF_INT_VALUE,
219 X_DIRECTION,
220 X_CREATION_DATE,
221 X_CREATED_BY,
222 X_LAST_UPDATE_DATE,
223 X_LAST_UPDATED_BY,
224 X_LAST_UPDATE_LOGIN
225 );
226
227 insert into ECX_XREF_DTL_TL (
228 XREF_DTL_ID,
229 DESCRIPTION,
230 LAST_UPDATE_DATE,
231 LAST_UPDATED_BY,
232 CREATION_DATE,
233 CREATED_BY,
234 LAST_UPDATE_LOGIN,
235 LANGUAGE,
236 SOURCE_LANG
237 ) select
238 X_XREF_DTL_ID,
239 X_DESCRIPTION,
240 X_LAST_UPDATE_DATE,
241 X_LAST_UPDATED_BY,
242 X_CREATION_DATE,
243 X_CREATED_BY,
244 X_LAST_UPDATE_LOGIN,
245 L.CODE,
246 userenv('LANG')
247 from WF_LANGUAGES L
248 where L.INSTALLED_FLAG = 'Y'
249 and not exists
250 (select NULL
251 from ECX_XREF_DTL_TL T
252 where T.XREF_DTL_ID = X_XREF_DTL_ID
253 and T.LANGUAGE = L.CODE);
254
255 open c;
256 fetch c into X_ROWID;
257 if (c%notfound) then
258 close c;
259 raise no_data_found;
260 end if;
261 close c;
262
263 end INSERT_ROW;
264
265 procedure LOCK_ROW (
266 X_XREF_DTL_ID in NUMBER,
267 X_XREF_CATEGORY_ID in NUMBER,
268 X_STANDARD_ID in NUMBER,
269 X_XREF_STANDARD_CODE in VARCHAR2,
270 X_TP_HEADER_ID in NUMBER,
271 X_XREF_EXT_VALUE in VARCHAR2,
272 X_XREF_INT_VALUE in VARCHAR2,
273 X_DIRECTION in VARCHAR2,
274 X_DESCRIPTION in VARCHAR2
275 ) is
276 cursor c is select
277 XREF_CATEGORY_ID,
278 STANDARD_ID,
279 XREF_STANDARD_CODE,
280 TP_HEADER_ID,
281 XREF_EXT_VALUE,
282 XREF_INT_VALUE,
283 DIRECTION
284 from ECX_XREF_DTL_B
285 where XREF_DTL_ID = X_XREF_DTL_ID
286 for update of XREF_DTL_ID nowait;
287 recinfo c%rowtype;
288
289 cursor c1 is select
290 DESCRIPTION,
291 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
292 from ECX_XREF_DTL_TL
293 where XREF_DTL_ID = X_XREF_DTL_ID
294 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
295 for update of XREF_DTL_ID nowait;
296 begin
297 open c;
298 fetch c into recinfo;
299 if (c%notfound) then
300 close c;
301 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
302 app_exception.raise_exception;
303 end if;
304 close c;
305 if ( (recinfo.XREF_CATEGORY_ID = X_XREF_CATEGORY_ID)
306 AND (recinfo.STANDARD_ID = X_STANDARD_ID)
307 AND ((recinfo.XREF_STANDARD_CODE = X_XREF_STANDARD_CODE)
308 OR ((recinfo.XREF_STANDARD_CODE is null) AND (X_XREF_STANDARD_CODE is null)))
309 AND (recinfo.TP_HEADER_ID = X_TP_HEADER_ID)
310 AND (recinfo.XREF_EXT_VALUE = X_XREF_EXT_VALUE)
311 AND (recinfo.XREF_INT_VALUE = X_XREF_INT_VALUE)
312 AND (recinfo.DIRECTION = X_DIRECTION)
313 ) then
314 null;
315 else
316 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
317 app_exception.raise_exception;
318 end if;
319
320 for tlinfo in c1 loop
321 if (tlinfo.BASELANG = 'Y') then
322 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
323 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
324 ) then
325 null;
326 else
327 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
328 app_exception.raise_exception;
329 end if;
330 end if;
331 end loop;
332 return;
333 end LOCK_ROW;
334
335 procedure UPDATE_ROW (
336 X_XREF_DTL_ID in NUMBER,
337 X_XREF_CATEGORY_ID in NUMBER,
338 X_STANDARD_ID in NUMBER,
339 X_XREF_STANDARD_CODE in VARCHAR2,
340 X_TP_HEADER_ID in NUMBER,
341 X_XREF_EXT_VALUE in VARCHAR2,
342 X_XREF_INT_VALUE in VARCHAR2,
343 X_DIRECTION in VARCHAR2,
344 X_DESCRIPTION in VARCHAR2,
345 X_LAST_UPDATE_DATE in DATE,
346 X_LAST_UPDATED_BY in NUMBER,
347 X_LAST_UPDATE_LOGIN in NUMBER
348 ) is
349 begin
350 update ECX_XREF_DTL_B set
351 XREF_CATEGORY_ID = X_XREF_CATEGORY_ID,
352 STANDARD_ID = X_STANDARD_ID,
353 XREF_STANDARD_CODE = X_XREF_STANDARD_CODE,
354 TP_HEADER_ID = X_TP_HEADER_ID,
358 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
355 XREF_EXT_VALUE = X_XREF_EXT_VALUE,
356 XREF_INT_VALUE = X_XREF_INT_VALUE,
357 DIRECTION = X_DIRECTION,
359 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
360 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
361 where XREF_DTL_ID = X_XREF_DTL_ID;
362
363 if (sql%notfound) then
364 raise no_data_found;
365 end if;
366
367 update ECX_XREF_DTL_TL set
368 DESCRIPTION = X_DESCRIPTION,
369 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
370 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
371 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
372 SOURCE_LANG = userenv('LANG')
373 where XREF_DTL_ID = X_XREF_DTL_ID
374 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
375
376 if (sql%notfound) then
377 raise no_data_found;
378 end if;
379 end UPDATE_ROW;
380
381 procedure DELETE_ROW (
382 X_XREF_DTL_ID in NUMBER
383 ) is
384 begin
385 delete from ECX_XREF_DTL_TL
386 where XREF_DTL_ID = X_XREF_DTL_ID;
387
388 if (sql%notfound) then
389 raise no_data_found;
390 end if;
391
392 delete from ECX_XREF_DTL_B
393 where XREF_DTL_ID = X_XREF_DTL_ID;
394
395 if (sql%notfound) then
396 raise no_data_found;
397 end if;
398 end DELETE_ROW;
399
400 procedure ADD_LANGUAGE
401 is
402 begin
403 delete from ECX_XREF_DTL_TL T
404 where not exists
405 (select NULL
406 from ECX_XREF_DTL_B B
407 where B.XREF_DTL_ID = T.XREF_DTL_ID
408 );
409
410 update ECX_XREF_DTL_TL T set (
411 DESCRIPTION
412 ) = (select
413 B.DESCRIPTION
414 from ECX_XREF_DTL_TL B
415 where B.XREF_DTL_ID = T.XREF_DTL_ID
416 and B.LANGUAGE = T.SOURCE_LANG)
417 where (
418 T.XREF_DTL_ID,
419 T.LANGUAGE
420 ) in (select
421 SUBT.XREF_DTL_ID,
422 SUBT.LANGUAGE
423 from ECX_XREF_DTL_TL SUBB, ECX_XREF_DTL_TL SUBT
424 where SUBB.XREF_DTL_ID = SUBT.XREF_DTL_ID
425 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
426 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
427 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
428 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
429 ));
430
431 insert into ECX_XREF_DTL_TL (
432 XREF_DTL_ID,
433 DESCRIPTION,
434 LAST_UPDATE_DATE,
435 LAST_UPDATED_BY,
436 CREATION_DATE,
437 CREATED_BY,
438 LAST_UPDATE_LOGIN,
439 LANGUAGE,
440 SOURCE_LANG
441 ) select
442 B.XREF_DTL_ID,
443 B.DESCRIPTION,
444 B.LAST_UPDATE_DATE,
445 B.LAST_UPDATED_BY,
446 B.CREATION_DATE,
447 B.CREATED_BY,
448 B.LAST_UPDATE_LOGIN,
449 L.CODE,
450 B.SOURCE_LANG
451 from ECX_XREF_DTL_TL B, WF_LANGUAGES L
452 where L.INSTALLED_FLAG = 'Y'
453 and B.LANGUAGE = userenv('LANG')
454 and not exists
455 (select NULL
456 from ECX_XREF_DTL_TL T
457 where T.XREF_DTL_ID = B.XREF_DTL_ID
458 and T.LANGUAGE = L.CODE);
459 end ADD_LANGUAGE;
460
461 end ECX_XREF_DTL_PKG;
[Home] [Help]