1 package body ECX_XREF_HDR_PKG as
2 /* $Header: ECXXRFHB.pls 120.2 2005/06/30 11:19:14 appldev ship $ */
3
4 procedure TRANSLATE_ROW (
5 X_XREF_CATEGORY_CODE IN VARCHAR2,
6 X_DESCRIPTION IN VARCHAR2,
7 X_OWNER IN VARCHAR2,
8 X_CUSTOM_MODE IN VARCHAR2
9 )
10 is
11 l_luby number; -- entity owner in file
12 l_ludate date; -- entity update date in file
13 l_db_luby number; -- entity owner in db
14 l_db_ludate date; -- entity update date in db
15 l_hdr_id number;
16 begin
17
18 -- Translate owner to file_last_updated_by
19 if (x_owner = 'SEED') then
20 l_luby := 1;
21 else
22 l_luby := 0;
23 end if;
24
25 l_ludate := sysdate;
26
27 begin
28 select xref_category_id
29 into l_hdr_id
30 from ecx_xref_hdr
31 where XREF_CATEGORY_CODE = x_xref_category_code;
32
33 select LAST_UPDATED_BY, LAST_UPDATE_DATE
34 into l_db_luby, l_db_ludate
35 from ECX_XREF_HDR_TL
36 where XREF_CATEGORY_ID = l_hdr_id
37 and LANGUAGE = userenv('LANG');
38
39 -- Update record, honoring customization mode.
40 -- Record should be updated only if:
41 -- a. CUSTOM_MODE = FORCE, or
42 -- b. file owner is CUSTOM, db owner is SEED
43 -- c. owners are the same, and file_date > db_date
44 if ((x_custom_mode = 'FORCE') or
45 ((l_luby = 0) and (l_db_luby = 1)) or
46 ((l_luby = l_db_luby) and (l_ludate > l_db_ludate)))
47 then
48 update ECX_XREF_HDR_TL set
49 DESCRIPTION = nvl(x_description, DESCRIPTION),
50 SOURCE_LANG = userenv('LANG'),
51 LAST_UPDATE_DATE = l_ludate,
52 LAST_UPDATED_BY = l_luby,
53 LAST_UPDATE_LOGIN = 0
54 where XREF_CATEGORY_ID = l_hdr_id
55 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
56 end if;
57 exception
58 when no_data_found then
59 null;
60 end;
61 end TRANSLATE_ROW;
62
63 procedure LOAD_ROW (
64 X_XREF_CATEGORY_CODE IN VARCHAR2,
65 X_DESCRIPTION IN VARCHAR2,
66 X_OWNER IN VARCHAR2,
67 X_CUSTOM_MODE IN VARCHAR2
68 )
69 is
70 profo_id number := 0;
71 user_id number := 0;
72 row_id varchar2(64);
73 l_luby number; -- entity owner in file
74 l_ludate date; -- entity update date in file
75 l_db_luby number; -- entity owner in db
76 l_db_ludate date; -- entity update date in db
77 l_ret_code pls_integer :=0;
78 l_errmsg varchar2(2000) := null;
79 l_hdr_id number := 0;
80 begin
81 -- Translate owner to file_last_updated_by
82 if (x_owner = 'SEED') then
83 l_luby := 1;
84 else
85 l_luby := 0;
86 end if;
87
88 -- Translate char last_update_date to date
89 l_ludate := sysdate;
90
91 begin
92 select XREF_CATEGORY_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE
93 into l_hdr_id, l_db_luby, l_db_ludate
94 from ECX_XREF_HDR_B
95 where XREF_CATEGORY_CODE = x_xref_category_code;
96
97 -- Update record, honoring customization mode.
98 -- Record should be updated only if:
99 -- a. CUSTOM_MODE = FORCE, or
100 -- b. file owner is CUSTOM, db owner is SEED
101 -- c. owners are the same, and file_date > db_date
102 if ((x_custom_mode = 'FORCE') or
103 ((l_luby = 0) and (l_db_luby = 1)) or
104 ((l_luby = l_db_luby) and (l_ludate > l_db_ludate)))
105 then
106 ecx_xref_api.update_code_category(
107 x_return_status => l_ret_code,
108 x_msg => l_errmsg,
109 p_xref_category_id => l_hdr_id,
110 p_xref_category_code => x_xref_category_code,
111 p_description => x_description,
112 p_owner => x_owner);
113 if NOT(l_ret_code = ECX_UTIL_API.G_NO_ERROR) then
114 raise_application_error(-20000, l_errmsg);
115 end if;
116
117 end if;
118 exception
119 when no_data_found then
120 ecx_xref_api.create_code_category(
121 x_return_status => l_ret_code,
122 x_msg => l_errmsg,
123 x_xref_hdr_id => l_hdr_id,
124 p_xref_category_code => x_xref_category_code,
125 p_description => x_description,
126 p_owner => x_owner);
127 if NOT(l_ret_code = ECX_UTIL_API.G_NO_ERROR) then
128 raise_application_error(-20000, l_errmsg);
129 end if;
130 when others then
131 raise;
132 end;
133 end LOAD_ROW;
134
135
136 procedure INSERT_ROW (
137 X_ROWID in out nocopy VARCHAR2,
138 X_XREF_CATEGORY_ID in NUMBER,
139 X_XREF_CATEGORY_CODE in VARCHAR2,
140 X_DESCRIPTION in VARCHAR2,
141 X_CREATION_DATE in DATE,
142 X_CREATED_BY in NUMBER,
143 X_LAST_UPDATE_DATE in DATE,
144 X_LAST_UPDATED_BY in NUMBER,
145 X_LAST_UPDATE_LOGIN in NUMBER
146 ) is
147 cursor C is select ROWID from ECX_XREF_HDR_B
148 where XREF_CATEGORY_ID = X_XREF_CATEGORY_ID
149 ;
150 begin
151 insert into ECX_XREF_HDR_B (
152 XREF_CATEGORY_ID,
153 XREF_CATEGORY_CODE,
154 CREATION_DATE,
155 CREATED_BY,
156 LAST_UPDATE_DATE,
157 LAST_UPDATED_BY,
158 LAST_UPDATE_LOGIN
159 ) values (
160 X_XREF_CATEGORY_ID,
161 X_XREF_CATEGORY_CODE,
162 X_CREATION_DATE,
163 X_CREATED_BY,
164 X_LAST_UPDATE_DATE,
165 X_LAST_UPDATED_BY,
166 X_LAST_UPDATE_LOGIN
167 );
168
169 insert into ECX_XREF_HDR_TL (
170 DESCRIPTION,
171 LAST_UPDATE_DATE,
172 LAST_UPDATED_BY,
173 CREATION_DATE,
174 CREATED_BY,
175 LAST_UPDATE_LOGIN,
176 XREF_CATEGORY_ID,
177 LANGUAGE,
178 SOURCE_LANG
179 ) select
180 X_DESCRIPTION,
181 X_LAST_UPDATE_DATE,
182 X_LAST_UPDATED_BY,
183 X_CREATION_DATE,
184 X_CREATED_BY,
185 X_LAST_UPDATE_LOGIN,
186 X_XREF_CATEGORY_ID,
187 L.CODE,
188 userenv('LANG')
189 from WF_LANGUAGES L
190 where L.INSTALLED_FLAG = 'Y'
191 and not exists
192 (select NULL
193 from ECX_XREF_HDR_TL T
194 where T.XREF_CATEGORY_ID = X_XREF_CATEGORY_ID
195 and T.LANGUAGE = L.CODE);
196
197 open c;
198 fetch c into X_ROWID;
199 if (c%notfound) then
200 close c;
201 raise no_data_found;
202 end if;
203 close c;
204
205 end INSERT_ROW;
206
207 procedure LOCK_ROW (
208 X_XREF_CATEGORY_ID in NUMBER,
209 X_XREF_CATEGORY_CODE in VARCHAR2,
210 X_DESCRIPTION in VARCHAR2
211 ) is
212 cursor c is select
213 XREF_CATEGORY_CODE
214 from ECX_XREF_HDR_B
215 where XREF_CATEGORY_ID = X_XREF_CATEGORY_ID
216 for update of XREF_CATEGORY_ID nowait;
217 recinfo c%rowtype;
218
219 cursor c1 is select
220 DESCRIPTION,
221 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
222 from ECX_XREF_HDR_TL
223 where XREF_CATEGORY_ID = X_XREF_CATEGORY_ID
224 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
225 for update of XREF_CATEGORY_ID nowait;
226 begin
227 open c;
228 fetch c into recinfo;
229 if (c%notfound) then
230 close c;
231 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
232 app_exception.raise_exception;
233 end if;
234 close c;
235 if ( (recinfo.XREF_CATEGORY_CODE = X_XREF_CATEGORY_CODE)
236 ) then
237 null;
238 else
239 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
240 app_exception.raise_exception;
241 end if;
242
243 for tlinfo in c1 loop
244 if (tlinfo.BASELANG = 'Y') then
245 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
246 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
247 ) then
248 null;
249 else
250 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
251 app_exception.raise_exception;
252 end if;
253 end if;
254 end loop;
255 return;
256 end LOCK_ROW;
257
258 procedure UPDATE_ROW (
259 X_XREF_CATEGORY_ID in NUMBER,
260 X_XREF_CATEGORY_CODE in VARCHAR2,
261 X_DESCRIPTION in VARCHAR2,
262 X_LAST_UPDATE_DATE in DATE,
263 X_LAST_UPDATED_BY in NUMBER,
264 X_LAST_UPDATE_LOGIN in NUMBER
265 ) is
266 begin
267 update ECX_XREF_HDR_B set
268 XREF_CATEGORY_CODE = X_XREF_CATEGORY_CODE,
269 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
270 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
271 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
272 where XREF_CATEGORY_ID = X_XREF_CATEGORY_ID;
273
274 if (sql%notfound) then
275 raise no_data_found;
276 end if;
277
278 update ECX_XREF_HDR_TL set
279 DESCRIPTION = X_DESCRIPTION,
280 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
281 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
282 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
283 SOURCE_LANG = userenv('LANG')
284 where XREF_CATEGORY_ID = X_XREF_CATEGORY_ID
285 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
286
287 if (sql%notfound) then
288 raise no_data_found;
289 end if;
290 end UPDATE_ROW;
291
292 procedure DELETE_ROW (
293 X_XREF_CATEGORY_ID in NUMBER
294 ) is
295 begin
296 delete from ECX_XREF_HDR_TL
297 where XREF_CATEGORY_ID = X_XREF_CATEGORY_ID;
298
299 if (sql%notfound) then
300 raise no_data_found;
301 end if;
302
303 delete from ECX_XREF_HDR_B
304 where XREF_CATEGORY_ID = X_XREF_CATEGORY_ID;
305
306 if (sql%notfound) then
307 raise no_data_found;
308 end if;
309 end DELETE_ROW;
310
311 procedure ADD_LANGUAGE
312 is
313 begin
314 delete from ECX_XREF_HDR_TL T
315 where not exists
316 (select NULL
317 from ECX_XREF_HDR_B B
318 where B.XREF_CATEGORY_ID = T.XREF_CATEGORY_ID
319 );
320
321 update ECX_XREF_HDR_TL T set (
322 DESCRIPTION
323 ) = (select
324 B.DESCRIPTION
325 from ECX_XREF_HDR_TL B
326 where B.XREF_CATEGORY_ID = T.XREF_CATEGORY_ID
327 and B.LANGUAGE = T.SOURCE_LANG)
328 where (
329 T.XREF_CATEGORY_ID,
330 T.LANGUAGE
331 ) in (select
332 SUBT.XREF_CATEGORY_ID,
333 SUBT.LANGUAGE
334 from ECX_XREF_HDR_TL SUBB, ECX_XREF_HDR_TL SUBT
335 where SUBB.XREF_CATEGORY_ID = SUBT.XREF_CATEGORY_ID
336 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
337 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
338 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
339 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
340 ));
341
342 insert into ECX_XREF_HDR_TL (
343 DESCRIPTION,
344 LAST_UPDATE_DATE,
345 LAST_UPDATED_BY,
346 CREATION_DATE,
347 CREATED_BY,
348 LAST_UPDATE_LOGIN,
349 XREF_CATEGORY_ID,
350 LANGUAGE,
351 SOURCE_LANG
352 ) select
353 B.DESCRIPTION,
354 B.LAST_UPDATE_DATE,
355 B.LAST_UPDATED_BY,
356 B.CREATION_DATE,
357 B.CREATED_BY,
358 B.LAST_UPDATE_LOGIN,
359 B.XREF_CATEGORY_ID,
360 L.CODE,
361 B.SOURCE_LANG
362 from ECX_XREF_HDR_TL B, WF_LANGUAGES L
363 where L.INSTALLED_FLAG = 'Y'
364 and B.LANGUAGE = userenv('LANG')
365 and not exists
366 (select NULL
367 from ECX_XREF_HDR_TL T
368 where T.XREF_CATEGORY_ID = B.XREF_CATEGORY_ID
369 and T.LANGUAGE = L.CODE);
370 end ADD_LANGUAGE;
371
372 end ECX_XREF_HDR_PKG;
[Home] [Help]