[Home] [Help]
PACKAGE BODY: APPS.XDO_DS_DEFINITIONS_PKG
Source
1 package body XDO_DS_DEFINITIONS_PKG as
2 /* $Header: XDODSDFB.pls 120.1 2005/07/02 05:05:29 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_APPLICATION_SHORT_NAME in VARCHAR2,
6 X_DATA_SOURCE_CODE in VARCHAR2,
7 X_DATA_SOURCE_STATUS in VARCHAR2,
8 X_START_DATE in DATE,
9 X_END_DATE in DATE,
10 X_OBJECT_VERSION_NUMBER in NUMBER,
11 X_ATTRIBUTE_CATEGORY in VARCHAR2,
12 X_ATTRIBUTE1 in VARCHAR2,
13 X_ATTRIBUTE2 in VARCHAR2,
14 X_ATTRIBUTE3 in VARCHAR2,
15 X_ATTRIBUTE4 in VARCHAR2,
16 X_ATTRIBUTE5 in VARCHAR2,
17 X_ATTRIBUTE6 in VARCHAR2,
18 X_ATTRIBUTE7 in VARCHAR2,
19 X_ATTRIBUTE8 in VARCHAR2,
20 X_ATTRIBUTE9 in VARCHAR2,
21 X_ATTRIBUTE10 in VARCHAR2,
22 X_ATTRIBUTE11 in VARCHAR2,
23 X_ATTRIBUTE12 in VARCHAR2,
24 X_ATTRIBUTE13 in VARCHAR2,
25 X_ATTRIBUTE14 in VARCHAR2,
26 X_ATTRIBUTE15 in VARCHAR2,
27 X_DATA_SOURCE_NAME in VARCHAR2,
28 X_DESCRIPTION in VARCHAR2,
29 X_CREATION_DATE in DATE,
30 X_CREATED_BY in NUMBER,
31 X_LAST_UPDATE_DATE in DATE,
32 X_LAST_UPDATED_BY in NUMBER,
33 X_LAST_UPDATE_LOGIN in NUMBER
34 ) is
35 cursor C is select ROWID from XDO_DS_DEFINITIONS_B
36 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
37 and DATA_SOURCE_CODE = X_DATA_SOURCE_CODE
38 ;
39 begin
40 insert into XDO_DS_DEFINITIONS_B (
41 APPLICATION_SHORT_NAME,
42 DATA_SOURCE_CODE,
43 DATA_SOURCE_STATUS,
44 START_DATE,
45 END_DATE,
46 OBJECT_VERSION_NUMBER,
47 ATTRIBUTE_CATEGORY,
48 ATTRIBUTE1,
49 ATTRIBUTE2,
50 ATTRIBUTE3,
51 ATTRIBUTE4,
52 ATTRIBUTE5,
53 ATTRIBUTE6,
54 ATTRIBUTE7,
55 ATTRIBUTE8,
56 ATTRIBUTE9,
57 ATTRIBUTE10,
58 ATTRIBUTE11,
59 ATTRIBUTE12,
60 ATTRIBUTE13,
61 ATTRIBUTE14,
62 ATTRIBUTE15,
63 CREATION_DATE,
64 CREATED_BY,
65 LAST_UPDATE_DATE,
66 LAST_UPDATED_BY,
67 LAST_UPDATE_LOGIN
68 ) values (
69 X_APPLICATION_SHORT_NAME,
70 X_DATA_SOURCE_CODE,
71 X_DATA_SOURCE_STATUS,
72 X_START_DATE,
73 X_END_DATE,
74 X_OBJECT_VERSION_NUMBER,
75 X_ATTRIBUTE_CATEGORY,
76 X_ATTRIBUTE1,
77 X_ATTRIBUTE2,
78 X_ATTRIBUTE3,
79 X_ATTRIBUTE4,
80 X_ATTRIBUTE5,
81 X_ATTRIBUTE6,
82 X_ATTRIBUTE7,
83 X_ATTRIBUTE8,
84 X_ATTRIBUTE9,
85 X_ATTRIBUTE10,
86 X_ATTRIBUTE11,
87 X_ATTRIBUTE12,
88 X_ATTRIBUTE13,
89 X_ATTRIBUTE14,
90 X_ATTRIBUTE15,
91 X_CREATION_DATE,
92 X_CREATED_BY,
93 X_LAST_UPDATE_DATE,
94 X_LAST_UPDATED_BY,
95 X_LAST_UPDATE_LOGIN
96 );
97
98 insert into XDO_DS_DEFINITIONS_TL (
99 APPLICATION_SHORT_NAME,
100 DATA_SOURCE_CODE,
101 DATA_SOURCE_NAME,
102 DESCRIPTION,
103 CREATED_BY,
104 CREATION_DATE,
105 LAST_UPDATED_BY,
106 LAST_UPDATE_DATE,
107 LAST_UPDATE_LOGIN,
108 LANGUAGE,
109 SOURCE_LANG
110 ) select
111 X_APPLICATION_SHORT_NAME,
112 X_DATA_SOURCE_CODE,
113 X_DATA_SOURCE_NAME,
114 X_DESCRIPTION,
115 X_CREATED_BY,
116 X_CREATION_DATE,
117 X_LAST_UPDATED_BY,
118 X_LAST_UPDATE_DATE,
119 X_LAST_UPDATE_LOGIN,
120 L.LANGUAGE_CODE,
121 userenv('LANG')
122 from FND_LANGUAGES L
123 where L.INSTALLED_FLAG in ('I', 'B')
124 and not exists
125 (select NULL
126 from XDO_DS_DEFINITIONS_TL T
127 where T.APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
128 and T.DATA_SOURCE_CODE = X_DATA_SOURCE_CODE
129 and T.LANGUAGE = L.LANGUAGE_CODE);
130
131 open c;
132 fetch c into X_ROWID;
133 if (c%notfound) then
134 close c;
135 raise no_data_found;
136 end if;
137 close c;
138
139 end INSERT_ROW;
140
141
142 procedure LOCK_ROW (
143 X_APPLICATION_SHORT_NAME in VARCHAR2,
144 X_DATA_SOURCE_CODE in VARCHAR2,
145 X_DATA_SOURCE_STATUS in VARCHAR2,
146 X_START_DATE in DATE,
147 X_END_DATE in DATE,
148 X_OBJECT_VERSION_NUMBER in NUMBER,
149 X_ATTRIBUTE_CATEGORY in VARCHAR2,
150 X_ATTRIBUTE1 in VARCHAR2,
151 X_ATTRIBUTE2 in VARCHAR2,
152 X_ATTRIBUTE3 in VARCHAR2,
153 X_ATTRIBUTE4 in VARCHAR2,
154 X_ATTRIBUTE5 in VARCHAR2,
155 X_ATTRIBUTE6 in VARCHAR2,
156 X_ATTRIBUTE7 in VARCHAR2,
157 X_ATTRIBUTE8 in VARCHAR2,
158 X_ATTRIBUTE9 in VARCHAR2,
159 X_ATTRIBUTE10 in VARCHAR2,
160 X_ATTRIBUTE11 in VARCHAR2,
161 X_ATTRIBUTE12 in VARCHAR2,
162 X_ATTRIBUTE13 in VARCHAR2,
163 X_ATTRIBUTE14 in VARCHAR2,
164 X_ATTRIBUTE15 in VARCHAR2,
165 X_DATA_SOURCE_NAME in VARCHAR2,
166 X_DESCRIPTION in VARCHAR2
167 ) is
168 cursor c is select
169 DATA_SOURCE_STATUS,
170 START_DATE,
171 END_DATE,
172 OBJECT_VERSION_NUMBER,
173 ATTRIBUTE_CATEGORY,
174 ATTRIBUTE1,
175 ATTRIBUTE2,
176 ATTRIBUTE3,
177 ATTRIBUTE4,
178 ATTRIBUTE5,
179 ATTRIBUTE6,
180 ATTRIBUTE7,
181 ATTRIBUTE8,
182 ATTRIBUTE9,
183 ATTRIBUTE10,
184 ATTRIBUTE11,
185 ATTRIBUTE12,
186 ATTRIBUTE13,
187 ATTRIBUTE14,
188 ATTRIBUTE15
189 from XDO_DS_DEFINITIONS_B
190 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
191 and DATA_SOURCE_CODE = X_DATA_SOURCE_CODE
192 for update of APPLICATION_SHORT_NAME nowait;
193 recinfo c%rowtype;
194
195 cursor c1 is select
196 DATA_SOURCE_NAME,
197 DESCRIPTION,
198 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
199 from XDO_DS_DEFINITIONS_TL
200 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
201 and DATA_SOURCE_CODE = X_DATA_SOURCE_CODE
202 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
203 for update of APPLICATION_SHORT_NAME nowait;
204 begin
205 open c;
206 fetch c into recinfo;
207 if (c%notfound) then
208 close c;
209 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
210 app_exception.raise_exception;
211 end if;
212 close c;
213 if ( (recinfo.DATA_SOURCE_STATUS = X_DATA_SOURCE_STATUS)
214 AND (recinfo.START_DATE = X_START_DATE)
215 AND ((recinfo.END_DATE = X_END_DATE)
216 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
217 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
218 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
219 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
220 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
221 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
222 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
223 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
224 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
225 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
226 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
227 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
228 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
229 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
230 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
231 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
232 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
233 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
234 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
235 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
236 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
237 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
238 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
239 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
240 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
241 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
242 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
243 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
244 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
245 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
246 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
247 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
248 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
249 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
250 ) then
251 null;
252 else
253 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
254 app_exception.raise_exception;
255 end if;
256
257 for tlinfo in c1 loop
258 if (tlinfo.BASELANG = 'Y') then
259 if ( (tlinfo.DATA_SOURCE_NAME = X_DATA_SOURCE_NAME)
260 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
261 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
262 ) then
263 null;
264 else
265 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
266 app_exception.raise_exception;
267 end if;
268 end if;
269 end loop;
270 return;
271 end LOCK_ROW;
272
273 procedure UPDATE_ROW (
274 X_APPLICATION_SHORT_NAME in VARCHAR2,
275 X_DATA_SOURCE_CODE in VARCHAR2,
276 X_DATA_SOURCE_STATUS in VARCHAR2,
277 X_START_DATE in DATE,
278 X_END_DATE in DATE,
279 X_OBJECT_VERSION_NUMBER in NUMBER,
280 X_ATTRIBUTE_CATEGORY in VARCHAR2,
281 X_ATTRIBUTE1 in VARCHAR2,
282 X_ATTRIBUTE2 in VARCHAR2,
283 X_ATTRIBUTE3 in VARCHAR2,
284 X_ATTRIBUTE4 in VARCHAR2,
285 X_ATTRIBUTE5 in VARCHAR2,
286 X_ATTRIBUTE6 in VARCHAR2,
287 X_ATTRIBUTE7 in VARCHAR2,
288 X_ATTRIBUTE8 in VARCHAR2,
289 X_ATTRIBUTE9 in VARCHAR2,
290 X_ATTRIBUTE10 in VARCHAR2,
291 X_ATTRIBUTE11 in VARCHAR2,
292 X_ATTRIBUTE12 in VARCHAR2,
293 X_ATTRIBUTE13 in VARCHAR2,
294 X_ATTRIBUTE14 in VARCHAR2,
295 X_ATTRIBUTE15 in VARCHAR2,
296 X_DATA_SOURCE_NAME in VARCHAR2,
297 X_DESCRIPTION in VARCHAR2,
298 X_LAST_UPDATE_DATE in DATE,
299 X_LAST_UPDATED_BY in NUMBER,
300 X_LAST_UPDATE_LOGIN in NUMBER
301 ) is
302 begin
303 update XDO_DS_DEFINITIONS_B set
304 DATA_SOURCE_STATUS = X_DATA_SOURCE_STATUS,
305 START_DATE = X_START_DATE,
306 END_DATE = X_END_DATE,
307 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
308 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
309 ATTRIBUTE1 = X_ATTRIBUTE1,
310 ATTRIBUTE2 = X_ATTRIBUTE2,
311 ATTRIBUTE3 = X_ATTRIBUTE3,
312 ATTRIBUTE4 = X_ATTRIBUTE4,
313 ATTRIBUTE5 = X_ATTRIBUTE5,
314 ATTRIBUTE6 = X_ATTRIBUTE6,
315 ATTRIBUTE7 = X_ATTRIBUTE7,
316 ATTRIBUTE8 = X_ATTRIBUTE8,
317 ATTRIBUTE9 = X_ATTRIBUTE9,
318 ATTRIBUTE10 = X_ATTRIBUTE10,
319 ATTRIBUTE11 = X_ATTRIBUTE11,
320 ATTRIBUTE12 = X_ATTRIBUTE12,
321 ATTRIBUTE13 = X_ATTRIBUTE13,
322 ATTRIBUTE14 = X_ATTRIBUTE14,
323 ATTRIBUTE15 = X_ATTRIBUTE15,
324 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
325 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
326 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
327 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
328 and DATA_SOURCE_CODE = X_DATA_SOURCE_CODE;
329
330 if (sql%notfound) then
331 raise no_data_found;
332 end if;
333
334 update XDO_DS_DEFINITIONS_TL set
335 DATA_SOURCE_NAME = X_DATA_SOURCE_NAME,
336 DESCRIPTION = X_DESCRIPTION,
337 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
338 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
339 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
340 SOURCE_LANG = userenv('LANG')
341 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
342 and DATA_SOURCE_CODE = X_DATA_SOURCE_CODE
343 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
344
345 if (sql%notfound) then
346 raise no_data_found;
347 end if;
348 end UPDATE_ROW;
349
350 procedure DELETE_ROW (
351 X_APPLICATION_SHORT_NAME in VARCHAR2,
352 X_DATA_SOURCE_CODE in VARCHAR2
353 ) is
354 begin
355 delete from XDO_DS_DEFINITIONS_TL
356 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
357 and DATA_SOURCE_CODE = X_DATA_SOURCE_CODE;
358
359 if (sql%notfound) then
360 raise no_data_found;
361 end if;
362
363 delete from XDO_DS_DEFINITIONS_B
364 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
365 and DATA_SOURCE_CODE = X_DATA_SOURCE_CODE;
366
367 if (sql%notfound) then
368 raise no_data_found;
369 end if;
370 end DELETE_ROW;
371
372 procedure ADD_LANGUAGE
373 is
374 begin
375 delete from XDO_DS_DEFINITIONS_TL T
376 where not exists
377 (select NULL
378 from XDO_DS_DEFINITIONS_B B
379 where B.APPLICATION_SHORT_NAME = T.APPLICATION_SHORT_NAME
380 and B.DATA_SOURCE_CODE = T.DATA_SOURCE_CODE
381 );
382
383 update XDO_DS_DEFINITIONS_TL T set (
384 DATA_SOURCE_NAME,
385 DESCRIPTION
386 ) = (select
387 B.DATA_SOURCE_NAME,
388 B.DESCRIPTION
389 from XDO_DS_DEFINITIONS_TL B
390 where B.APPLICATION_SHORT_NAME = T.APPLICATION_SHORT_NAME
391 and B.DATA_SOURCE_CODE = T.DATA_SOURCE_CODE
392 and B.LANGUAGE = T.SOURCE_LANG)
393 where (
394 T.APPLICATION_SHORT_NAME,
395 T.DATA_SOURCE_CODE,
396 T.LANGUAGE
397 ) in (select
398 SUBT.APPLICATION_SHORT_NAME,
399 SUBT.DATA_SOURCE_CODE,
400 SUBT.LANGUAGE
401 from XDO_DS_DEFINITIONS_TL SUBB, XDO_DS_DEFINITIONS_TL SUBT
402 where SUBB.APPLICATION_SHORT_NAME = SUBT.APPLICATION_SHORT_NAME
403 and SUBB.DATA_SOURCE_CODE = SUBT.DATA_SOURCE_CODE
404 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
405 and (SUBB.DATA_SOURCE_NAME <> SUBT.DATA_SOURCE_NAME
406 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
407 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
408 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
409 ));
410 insert into XDO_DS_DEFINITIONS_TL (
411 APPLICATION_SHORT_NAME,
412 DATA_SOURCE_CODE,
413 DATA_SOURCE_NAME,
414 DESCRIPTION,
415 CREATED_BY,
416 CREATION_DATE,
417 LAST_UPDATED_BY,
418 LAST_UPDATE_DATE,
419 LAST_UPDATE_LOGIN,
420 LANGUAGE,
421 SOURCE_LANG
422 ) select /*+ ORDERED */
423 B.APPLICATION_SHORT_NAME,
424 B.DATA_SOURCE_CODE,
425 B.DATA_SOURCE_NAME,
426 B.DESCRIPTION,
427 B.CREATED_BY,
428 B.CREATION_DATE,
429 B.LAST_UPDATED_BY,
433 B.SOURCE_LANG
430 B.LAST_UPDATE_DATE,
431 B.LAST_UPDATE_LOGIN,
432 L.LANGUAGE_CODE,
434 from XDO_DS_DEFINITIONS_TL B, FND_LANGUAGES L
435 where L.INSTALLED_FLAG in ('I', 'B')
436 and B.LANGUAGE = userenv('LANG')
437 and not exists
438 (select NULL
439 from XDO_DS_DEFINITIONS_TL T
440 where T.APPLICATION_SHORT_NAME = B.APPLICATION_SHORT_NAME
441 and T.DATA_SOURCE_CODE = B.DATA_SOURCE_CODE
442 and T.LANGUAGE = L.LANGUAGE_CODE);
443 end ADD_LANGUAGE;
444
445 procedure TRANSLATE_ROW (
446 X_APPLICATION_SHORT_NAME in VARCHAR2,
447 X_DATA_SOURCE_CODE in VARCHAR2,
448 X_DATA_SOURCE_NAME in VARCHAR2,
449 X_DESCRIPTION in VARCHAR2,
450 X_OWNER in VARCHAR2,
451 X_LAST_UPDATE_DATE in VARCHAR2,
452 X_CUSTOM_MODE in VARCHAR2
453 ) is
454 f_luby number; -- entity owner in file
455 f_ludate date; -- entity update date in file
456 db_luby number; -- entity owner in db
457 db_ludate date; -- entity update date in db
458 begin
459
460 -- Translate owner to file_last_updated_by
461 f_luby := fnd_load_util.OWNER_ID(x_owner);
462
463 -- Translate char last_update_date to date
464 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
465
466
467 begin
468 select LAST_UPDATED_BY, LAST_UPDATE_DATE
469 into db_luby, db_ludate
470 from XDO_DS_DEFINITIONS_TL
471 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
472 and DATA_SOURCE_CODE = X_DATA_SOURCE_CODE
473 and LANGUAGE = userenv('LANG');
474
475 -- Update record, honoring customization mode.
476 -- Record should be updated only if:
477 -- a. CUSTOM_MODE = FORCE, or
478 -- b. file owner is USER, db owner is SEED
479 -- c. owners are the same, and file_date > db_date
480 if (fnd_load_util.UPLOAD_TEST(
481 p_file_id => f_luby,
482 p_file_lud => f_ludate,
483 p_db_id => db_luby,
484 p_db_lud => db_ludate,
485 p_custom_mode => x_custom_mode))
486 then
487 update XDO_DS_DEFINITIONS_TL set
488 DATA_SOURCE_NAME =
489 nvl(x_data_source_name, data_source_name),
490 DESCRIPTION = nvl(x_description, DESCRIPTION),
491 SOURCE_LANG = userenv('LANG'),
492 LAST_UPDATE_DATE = f_ludate,
493 LAST_UPDATED_BY = f_luby,
494 LAST_UPDATE_LOGIN = 0
495 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
496 and DATA_SOURCE_CODE = X_DATA_SOURCE_CODE
497 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
498 end if;
499 exception
500 when no_data_found then
501 null;
502 end;
503
504 end TRANSLATE_ROW;
505
506 procedure LOAD_ROW (
507 X_APPLICATION_SHORT_NAME in VARCHAR2,
508 X_DATA_SOURCE_CODE in VARCHAR2,
509 X_DATA_SOURCE_STATUS in VARCHAR2,
510 X_START_DATE in VARCHAR2,
511 X_END_DATE in VARCHAR2,
512 X_OBJECT_VERSION_NUMBER in NUMBER,
513 X_DATA_SOURCE_NAME in VARCHAR2,
514 X_DESCRIPTION in VARCHAR2,
515 X_OWNER in VARCHAR2,
516 X_LAST_UPDATE_DATE in VARCHAR2,
517 X_CUSTOM_MODE in VARCHAR2,
518 X_ATTRIBUTE_CATEGORY in VARCHAR2,
519 X_ATTRIBUTE1 in VARCHAR2,
520 X_ATTRIBUTE2 in VARCHAR2,
521 X_ATTRIBUTE3 in VARCHAR2,
522 X_ATTRIBUTE4 in VARCHAR2,
523 X_ATTRIBUTE5 in VARCHAR2,
524 X_ATTRIBUTE6 in VARCHAR2,
525 X_ATTRIBUTE7 in VARCHAR2,
526 X_ATTRIBUTE8 in VARCHAR2,
527 X_ATTRIBUTE9 in VARCHAR2,
528 X_ATTRIBUTE10 in VARCHAR2,
532 X_ATTRIBUTE14 in VARCHAR2,
529 X_ATTRIBUTE11 in VARCHAR2,
530 X_ATTRIBUTE12 in VARCHAR2,
531 X_ATTRIBUTE13 in VARCHAR2,
533 X_ATTRIBUTE15 in VARCHAR2
534 ) is
535
536 f_luby number; -- entity owner in file
537 f_ludate date; -- entity update date in file
538 db_luby number; -- entity owner in db
539 db_ludate date; -- entity update date in db
540 row_id varchar2(64);
541
542 begin
543
544 -- Translate owner to file_last_updated_by
545 f_luby := fnd_load_util.owner_id(x_owner);
546
547 -- Translate char last_update_date to date
548 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
549
550 begin
551 select LAST_UPDATED_BY, LAST_UPDATE_DATE
552 into db_luby, db_ludate
553 from xdo_ds_definitions_b
554 where data_source_code=x_data_source_code
555 and application_short_name = X_APPLICATION_SHORT_NAME;
556
557 -- Update record, honoring customization mode.
558 -- Record should be updated only if:
559 -- a. CUSTOM_MODE = FORCE, or
560 -- b. file owner is CUSTOM, db owner is SEED
561 -- c. owners are the same, and file_date > db_date
562 if (fnd_load_util.UPLOAD_TEST(
563 p_file_id => f_luby,
564 p_file_lud => f_ludate,
565 p_db_id => db_luby,
566 p_db_lud => db_ludate,
567 p_custom_mode => x_custom_mode))
568 then
569 XDO_DS_DEFINITIONS_PKG.UPDATE_ROW(
570 X_APPLICATION_SHORT_NAME => X_APPLICATION_SHORT_NAME,
571 X_DATA_SOURCE_CODE => X_DATA_SOURCE_CODE,
572 X_DATA_SOURCE_STATUS => X_DATA_SOURCE_STATUS,
573 X_START_DATE => to_date(X_START_DATE, 'YYYY/MM/DD'),
574 X_END_DATE => to_date(X_END_DATE, 'YYYY/MM/DD'),
575 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
576 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
577 X_ATTRIBUTE1 => X_ATTRIBUTE1,
578 X_ATTRIBUTE2 => X_ATTRIBUTE2,
579 X_ATTRIBUTE3 => X_ATTRIBUTE3,
580 X_ATTRIBUTE4 => X_ATTRIBUTE4,
581 X_ATTRIBUTE5 => X_ATTRIBUTE5,
582 X_ATTRIBUTE6 => X_ATTRIBUTE6,
583 X_ATTRIBUTE7 => X_ATTRIBUTE7,
584 X_ATTRIBUTE8 => X_ATTRIBUTE8,
585 X_ATTRIBUTE9 => X_ATTRIBUTE9,
586 X_ATTRIBUTE10 => X_ATTRIBUTE10,
587 X_ATTRIBUTE11 => X_ATTRIBUTE11,
588 X_ATTRIBUTE12 => X_ATTRIBUTE12,
589 X_ATTRIBUTE13 => X_ATTRIBUTE13,
590 X_ATTRIBUTE14 => X_ATTRIBUTE14,
591 X_ATTRIBUTE15 => X_ATTRIBUTE15,
592 X_DATA_SOURCE_NAME => X_DATA_SOURCE_NAME,
593 X_DESCRIPTION => X_DESCRIPTION,
597 );
594 X_LAST_UPDATE_DATE => f_ludate,
595 X_LAST_UPDATED_BY => f_luby,
596 X_LAST_UPDATE_LOGIN => 0
598 end if;
599
600 exception when no_data_found then
601
602 XDO_DS_DEFINITIONS_PKG.INSERT_ROW(
603 X_ROWID => row_id,
604 X_APPLICATION_SHORT_NAME => X_APPLICATION_SHORT_NAME,
605 X_DATA_SOURCE_CODE => X_DATA_SOURCE_CODE,
606 X_DATA_SOURCE_STATUS => X_DATA_SOURCE_STATUS,
607 X_START_DATE => to_date(X_START_DATE, 'YYYY/MM/DD'),
608 X_END_DATE => to_date(X_END_DATE, 'YYYY/MM/DD'),
609 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
610 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
611 X_ATTRIBUTE1 => X_ATTRIBUTE1,
612 X_ATTRIBUTE2 => X_ATTRIBUTE2,
613 X_ATTRIBUTE3 => X_ATTRIBUTE3,
614 X_ATTRIBUTE4 => X_ATTRIBUTE4,
615 X_ATTRIBUTE5 => X_ATTRIBUTE5,
616 X_ATTRIBUTE6 => X_ATTRIBUTE6,
617 X_ATTRIBUTE7 => X_ATTRIBUTE7,
618 X_ATTRIBUTE8 => X_ATTRIBUTE8,
619 X_ATTRIBUTE9 => X_ATTRIBUTE9,
620 X_ATTRIBUTE10 => X_ATTRIBUTE10,
621 X_ATTRIBUTE11 => X_ATTRIBUTE11,
622 X_ATTRIBUTE12 => X_ATTRIBUTE12,
623 X_ATTRIBUTE13 => X_ATTRIBUTE13,
624 X_ATTRIBUTE14 => X_ATTRIBUTE14,
625 X_ATTRIBUTE15 => X_ATTRIBUTE15,
626 X_DATA_SOURCE_NAME => X_DATA_SOURCE_NAME,
627 X_DESCRIPTION => X_DESCRIPTION,
628 X_CREATION_DATE => f_ludate,
629 X_CREATED_BY => f_luby,
630 X_LAST_UPDATE_DATE => f_ludate,
631 X_LAST_UPDATED_BY => f_luby,
632 X_LAST_UPDATE_LOGIN => 0
633 );
634 end;
635
636 end LOAD_ROW;
637
638 end XDO_DS_DEFINITIONS_PKG;