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