[Home] [Help]
PACKAGE BODY: APPS.XDO_TRANS_UNITS_PKG
Source
1 package body XDO_TRANS_UNITS_PKG as
2 /* $Header: XDOTRUTB.pls 120.1 2005/07/02 05:05:42 appldev noship $ */
3
4 procedure INSERT_ROW (
5 X_APPLICATION_SHORT_NAME in VARCHAR2,
6 X_TEMPLATE_CODE in VARCHAR2,
7 X_UNIT_ID in VARCHAR2,
8 X_LANGUAGE in VARCHAR2,
9 X_TERRITORY in VARCHAR2,
10 X_VALUE in VARCHAR2,
11 X_NOTE in VARCHAR2,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 begin
19 insert into XDO_TRANS_UNITS (
20 APPLICATION_SHORT_NAME,
21 TEMPLATE_CODE,
22 UNIT_ID,
23 NOTE,
24 CREATION_DATE,
25 CREATED_BY,
26 LAST_UPDATE_DATE,
27 LAST_UPDATED_BY,
28 LAST_UPDATE_LOGIN
29 ) values (
30 X_APPLICATION_SHORT_NAME,
31 X_TEMPLATE_CODE,
32 X_UNIT_ID,
33 X_NOTE,
34 X_CREATION_DATE,
35 X_CREATED_BY,
36 X_LAST_UPDATE_DATE,
37 X_LAST_UPDATED_BY,
38 X_LAST_UPDATE_LOGIN
39 );
40
41 insert into XDO_TRANS_UNIT_VALUES (
42 APPLICATION_SHORT_NAME,
43 TEMPLATE_CODE,
44 UNIT_ID,
45 LANGUAGE,
46 TERRITORY,
47 VALUE,
48 CREATION_DATE,
49 CREATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_LOGIN
53 ) values (
54 X_APPLICATION_SHORT_NAME,
55 X_TEMPLATE_CODE,
56 X_UNIT_ID,
57 X_LANGUAGE,
58 X_TERRITORY,
59 X_VALUE,
60 X_CREATION_DATE,
61 X_CREATED_BY,
62 X_LAST_UPDATE_DATE,
63 X_LAST_UPDATED_BY,
64 X_LAST_UPDATE_LOGIN
65 );
66
67 end INSERT_ROW;
68
69 procedure UPDATE_ROW (
70 X_APPLICATION_SHORT_NAME in VARCHAR2,
71 X_TEMPLATE_CODE in VARCHAR2,
72 X_UNIT_ID in VARCHAR2,
73 X_LANGUAGE in VARCHAR2,
74 X_TERRITORY in VARCHAR2,
75 X_VALUE in VARCHAR2,
76 X_NOTE in VARCHAR2,
77 X_LAST_UPDATE_DATE in DATE,
78 X_LAST_UPDATED_BY in NUMBER,
79 X_LAST_UPDATE_LOGIN in NUMBER)
80 is
81 begin
82 update XDO_TRANS_UNITS
83 set APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME,
84 TEMPLATE_CODE = X_TEMPLATE_CODE,
85 UNIT_ID = X_UNIT_ID,
86 NOTE = nvl(X_NOTE, NOTE),
87 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
88 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
89 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
90 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
91 and TEMPLATE_CODE = X_TEMPLATE_CODE
92 and UNIT_ID = X_UNIT_ID;
93
94 if (sql%notfound) then
95 raise no_data_found;
96 end if;
97
98 update XDO_TRANS_UNIT_VALUES
99 set VALUE = X_VALUE,
100 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
101 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
102 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
103 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
104 and TEMPLATE_CODE = X_TEMPLATE_CODE
105 and LANGUAGE = X_LANGUAGE
106 and TERRITORY = X_TERRITORY
107 and UNIT_ID = X_UNIT_ID;
108
109 if (sql%notfound) then
110 translate_row(x_application_short_name, x_template_code, x_unit_id, x_language, x_territory, x_value, 'FORCE', x_last_update_date, x_last_updated_by, x_last_update_login);
111 end if;
112 end UPDATE_ROW;
113
114 procedure DELETE_ROW (
115 X_APPLICATION_SHORT_NAME in VARCHAR2,
116 X_TEMPLATE_CODE in VARCHAR2,
117 X_UNIT_ID in VARCHAR2
118 ) is
119 begin
120 delete from XDO_TRANS_UNIT_VALUES
121 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
122 and TEMPLATE_CODE = X_TEMPLATE_CODE
123 and UNIT_ID = X_UNIT_ID;
124
125 if (sql%notfound) then
126 raise no_data_found;
127 end if;
128
129 delete from XDO_TRANS_UNITS
130 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
131 and TEMPLATE_CODE = X_TEMPLATE_CODE
132 and UNIT_ID = UNIT_ID;
133
134 if (sql%notfound) then
135 raise no_data_found;
136 end if;
137 end DELETE_ROW;
138
139
140
141 procedure TRANSLATE_ROW (
142 X_APPLICATION_SHORT_NAME in VARCHAR2,
143 X_TEMPLATE_CODE in VARCHAR2,
144 X_UNIT_ID in VARCHAR2,
145 X_VALUE in VARCHAR2,
146 X_CUSTOM_MODE in VARCHAR2,
147 X_LAST_UPDATE_DATE in DATE,
148 X_LAST_UPDATED_BY in VARCHAR2,
149 X_LAST_UPDATE_LOGIN in VARCHAR2
150 ) is
151
152 l_lang VARCHAR2(2);
153 l_terr VARCHAR2(2);
154
155 begin
156
157 select lower(iso_language), iso_territory
158 into l_lang, l_terr
159 from fnd_languages
160 where language_code = userenv('LANG');
161
162 translate_row(x_application_short_name, x_template_code, x_unit_id, l_lang, l_terr, x_value, x_custom_mode, x_last_update_date, x_last_updated_by, x_last_update_login);
163
164 end translate_row;
165
166
167
168 procedure TRANSLATE_ROW (
169 X_APPLICATION_SHORT_NAME in VARCHAR2,
170 X_TEMPLATE_CODE in VARCHAR2,
171 X_UNIT_ID in VARCHAR2,
172 X_LANGUAGE in VARCHAR2,
173 X_TERRITORY in VARCHAR2,
174 X_VALUE in VARCHAR2,
175 X_CUSTOM_MODE in VARCHAR2,
176 X_LAST_UPDATE_DATE in DATE,
177 X_LAST_UPDATED_BY in VARCHAR2,
178 X_LAST_UPDATE_LOGIN in VARCHAR2
179 ) is
180 f_luby number; -- entity owner in file
181 f_ludate date; -- entity update date in file
182 db_luby number; -- entity owner in db
183 db_ludate date; -- entity update date in db
184 xtu_ludate date; -- lud in xdo_trans_units (to check the row exists)
185
186 begin
187
188 -- Translate char last_update_date to date
189 -- f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
190
191 begin
192 select LAST_UPDATED_BY, LAST_UPDATE_DATE
193 into db_luby, db_ludate
194 from XDO_TRANS_UNIT_VALUES
195 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
196 and TEMPLATE_CODE = X_TEMPLATE_CODE
197 and UNIT_ID = X_UNIT_ID
198 and language = X_LANGUAGE
199 and territory = X_TERRITORY;
200
201 -- Update record, honoring customization mode.
202 -- Record should be updated only if:
203 -- a. CUSTOM_MODE = FORCE, or
204 -- b. file owner is CUSTOM, db owner is SEED
205 -- c. owners are the same, and file_date > db_date
206 if (fnd_load_util.UPLOAD_TEST(
207 p_file_id => x_last_updated_by,
208 p_file_lud => x_last_update_date,
209 p_db_id => db_luby,
210 p_db_lud => db_ludate,
211 p_custom_mode => x_custom_mode))
212 then
213 update XDO_TRANS_UNIT_VALUES
214 set VALUE = X_VALUE,
215 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
216 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
217 LAST_UPDATE_LOGIN = 0
218 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
219 and TEMPLATE_CODE = X_TEMPLATE_CODE
220 and UNIT_ID = X_UNIT_ID
221 and LANGUAGE = X_LANGUAGE
222 and TERRITORY = X_TERRITORY;
223 end if;
224
225 exception
226 when no_data_found then
227
228 -- Check first if this is a valid trans-unit that exists
229 -- in XDO_TRANS_UNITS.
230 -- We should not create any new trans-units from this
231 -- procedure.
232 begin
233 select LAST_UPDATE_DATE
234 into xtu_ludate
235 from XDO_TRANS_UNITS
236 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
237 and TEMPLATE_CODE = X_TEMPLATE_CODE
238 and UNIT_ID = X_UNIT_ID;
239 exception
240 when no_data_found then
241 return;
242 end;
243
244 insert into XDO_TRANS_UNIT_VALUES (
245 APPLICATION_SHORT_NAME,
246 TEMPLATE_CODE,
247 UNIT_ID,
248 LANGUAGE,
249 TERRITORY,
250 VALUE,
251 LAST_UPDATE_DATE,
252 LAST_UPDATED_BY,
253 LAST_UPDATE_LOGIN,
254 CREATION_DATE,
255 CREATED_BY)
256 values (
257 X_APPLICATION_SHORT_NAME,
258 X_TEMPLATE_CODE,
259 X_UNIT_ID,
260 X_LANGUAGE,
261 X_TERRITORY,
262 X_VALUE,
263 X_LAST_UPDATE_DATE,
264 X_LAST_UPDATED_BY,
265 X_LAST_UPDATE_LOGIN,
266 SYSDATE,
267 X_LAST_UPDATED_BY);
268 end;
269 end TRANSLATE_ROW;
270
271 procedure LOAD_ROW (
272 X_APPLICATION_SHORT_NAME in VARCHAR2,
273 X_TEMPLATE_CODE in VARCHAR2,
274 X_UNIT_ID in VARCHAR2,
275 X_VALUE in VARCHAR2,
276 X_NOTE in VARCHAR2,
277 X_CUSTOM_MODE in VARCHAR2,
278 X_LAST_UPDATE_DATE in DATE,
279 X_OWNER in VARCHAR2)
280 is
281
282 f_luby NUMBER;
283 f_ludate DATE;
284
285 l_lang VARCHAR2(2);
286 l_terr VARCHAR2(2);
287
288 retval NUMBER;
289
290 begin
291
292 select lower(iso_language), iso_territory
293 into l_lang, l_terr
294 from fnd_languages
295 where language_code = userenv('LANG');
296
297 -- Translate owner to last_updated_by
298 f_luby := fnd_load_util.owner_id(x_owner);
299
300 f_ludate := nvl(x_last_update_date, sysdate);
301
302 retval := load_row(x_application_short_name,
303 x_template_code,
304 x_unit_id,
305 l_lang,
306 l_terr,
307 x_value,
308 x_note,
309 x_custom_mode,
310 f_ludate,
311 f_luby,
312 0);
313
314 end LOAD_ROW;
315
316 function LOAD_ROW (
317 X_APPLICATION_SHORT_NAME in VARCHAR2,
318 X_TEMPLATE_CODE in VARCHAR2,
319 X_UNIT_ID in VARCHAR2,
320 X_LANGUAGE in VARCHAR2,
321 X_TERRITORY in VARCHAR2,
322 X_VALUE in VARCHAR2,
323 X_NOTE in VARCHAR2,
324 X_CUSTOM_MODE in VARCHAR2,
325 X_LAST_UPDATE_DATE in DATE,
326 X_LAST_UPDATED_BY in VARCHAR2,
327 X_LAST_UPDATE_LOGIN in VARCHAR2) return number
328 is
329
330 db_luby number; -- entity owner in db
331 db_ludate date; -- entity update date in db
332
333 begin
334
335 begin
336
337 select LAST_UPDATED_BY, LAST_UPDATE_DATE
338 into db_luby, db_ludate
339 from XDO_TRANS_UNITS
340 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
341 and TEMPLATE_CODE = X_TEMPLATE_CODE
342 and UNIT_ID = X_UNIT_ID;
343
344 -- Update record, honoring customization mode.
345 -- Record should be updated only if:
346 -- a. CUSTOM_MODE = FORCE, or
347 -- b. file owner is CUSTOM, db owner is SEED
348 -- c. owners are the same, and file_date > db_date
349 if (fnd_load_util.UPLOAD_TEST(
350 p_file_id => x_last_updated_by,
351 p_file_lud => x_last_update_date,
352 p_db_id => db_luby,
353 p_db_lud => db_ludate,
354 p_custom_mode => x_custom_mode))
355 then
356
357 XDO_TRANS_UNITS_PKG.UPDATE_ROW(
358 X_APPLICATION_SHORT_NAME,
359 X_TEMPLATE_CODE,
360 X_UNIT_ID,
361 X_LANGUAGE,
362 X_TERRITORY,
363 X_VALUE,
364 X_NOTE,
365 X_LAST_UPDATE_DATE,
366 X_LAST_UPDATED_BY,
367 X_LAST_UPDATE_LOGIN
368 );
369
370 return 1; -- row updated
371
372 end if;
373
374 return 0; -- row not updated due to custom mode
375
376 exception when no_data_found then
377
378 XDO_TRANS_UNITS_PKG.INSERT_ROW(
379 X_APPLICATION_SHORT_NAME,
380 X_TEMPLATE_CODE,
381 X_UNIT_ID,
382 X_LANGUAGE,
383 X_TERRITORY,
384 X_VALUE,
385 X_NOTE,
386 X_LAST_UPDATE_DATE,
387 X_LAST_UPDATED_BY,
388 X_LAST_UPDATE_DATE,
389 X_LAST_UPDATED_BY,
390 X_LAST_UPDATE_LOGIN
391 );
392 return 2; -- row inserted
393 end;
394
395 end LOAD_ROW;
396
397 procedure LOAD_TRANS_UNIT_PROP (
398 X_APPLICATION_SHORT_NAME in VARCHAR2,
399 X_TEMPLATE_CODE in VARCHAR2,
400 X_UNIT_ID in VARCHAR2,
401 X_PROP_TYPE in VARCHAR2,
402 X_PROP_VALUE in VARCHAR2,
403 X_CUSTOM_MODE in VARCHAR2,
404 X_LAST_UPDATE_DATE in DATE,
405 X_LAST_UPDATED_BY in VARCHAR2,
406 X_LAST_UPDATE_LOGIN in VARCHAR2)
407 is
408
409 db_luby number; -- entity owner in db
410 db_ludate date; -- entity update date in db
411
412 begin
413
414 begin
415
416 select LAST_UPDATED_BY, LAST_UPDATE_DATE
417 into db_luby, db_ludate
418 from XDO_TRANS_UNIT_PROPS
419 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
420 and TEMPLATE_CODE = X_TEMPLATE_CODE
421 and UNIT_ID = X_UNIT_ID
422 and PROP_TYPE = X_PROP_TYPE;
423
424 if (fnd_load_util.UPLOAD_TEST(
425 p_file_id => x_last_updated_by,
426 p_file_lud => x_last_update_date,
427 p_db_id => db_luby,
428 p_db_lud => db_ludate,
429 p_custom_mode => x_custom_mode))
430 then
431
432 update xdo_trans_unit_props
433 set prop_value = X_PROP_VALUE,
434 last_update_date = X_LAST_UPDATE_DATE,
435 last_updated_by = X_LAST_UPDATED_BY,
436 last_update_login = X_LAST_UPDATE_LOGIN
437 where application_short_name = X_APPLICATION_SHORT_NAME
438 and template_code = X_TEMPLATE_CODE
439 and unit_id = X_UNIT_ID
440 and prop_type = X_PROP_TYPE;
441
442 end if;
443
444 exception when no_data_found then
445
446 insert into xdo_trans_unit_props
447 (
448 application_short_name,
449 template_code,
450 unit_id,
451 prop_type,
452 prop_value,
453 last_update_date,
454 last_updated_by,
455 creation_date,
456 created_by,
457 last_update_login )
458 values
459 (
460 X_APPLICATION_SHORT_NAME,
461 X_TEMPLATE_CODE,
462 X_UNIT_ID,
463 X_PROP_TYPE,
464 X_PROP_VALUE,
465 X_LAST_UPDATE_DATE,
466 X_LAST_UPDATED_BY,
467 X_LAST_UPDATE_DATE,
468 X_LAST_UPDATED_BY,
469 X_LAST_UPDATE_LOGIN);
470
471 end;
472
473 end LOAD_TRANS_UNIT_PROP;
474
475 end XDO_TRANS_UNITS_PKG;