[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_UMS_LOADER
Source
1 package body fnd_oam_ums_loader as
2 /* $Header: AFOAMUMSLDB.pls 120.1 2006/08/04 07:57:36 rjaiswal noship $ */
3
4 -- ==================================================
5 -- Constants and Types.
6 -- ==================================================
7
8 G_STD_DATE_MASK constant varchar2(100) := 'YYYY/MM/DD HH24:MI:SS';
9
10 DEBUG_OFF constant varchar2(10) := 'N';
11 DEBUG_ON constant varchar2(10) := 'Y';
12 DEBUG_STATS constant varchar2(10) := 'S';
13 DEBUG_ABORT constant varchar2(10) := 'A';
14
15 g_debug_flag varchar2(10);
16 g_newline varchar2(10);
17 g_default_date date;
18
19 --
20 -- lock life and wait times in terms of seconds
21 --
22 g_lock_lifetime integer := 1*24*60*60; -- one day
23 g_lock_waittime integer := 5*60; -- five minutes
24
25 g_bugfix_guid fnd_ums_bugfixes.bugfix_guid%type;
26
27 -- Prereqs/Includes/Links data level
28
29 PIL_LEVEL_NONE constant integer := 0;
30 PIL_LEVEL_PREREQS_INCLUDES constant integer := 1;
31 PIL_LEVEL_LINKS constant integer := 2;
32
33 -- Unknown data
34
35 NOT_AVAILABLE constant varchar2(10) := 'N/A';
36
37 -- UMS tables will be analyzed if
38 -- - analyze hasn't been run for more than TABLE_ANALYZE_PERIOD days or
39 -- - percentage of row count change is more than TABLE_ANALYZE_PERCENTAGE
40
41 TABLE_ANALYZE_PERIOD constant number := 28; -- 28 days (4 weeks)
42 TABLE_ANALYZE_PERCENTAGE constant number := 5; -- 5%
43
44 -- Error codes
45
46 ERROR_UNKNOWN_DOWNLOAD_MODE constant number := -20001;
47 ERROR_UNKNOWN_UPLOAD_PHASE constant number := -20002;
48 ERROR_UNABLE_TO_LOCK constant number := -20003;
49 ERROR_ABORT constant number := -20100;
50
51 -- Error Messages
52
53 MSG_ABORT_OLDER constant varchar2(100) := 'Since file data is older, aborted upload of bug ';
54 MSG_ABORT_LEVEL constant varchar2(100) := 'Since database data is at least as complete as file data, aborted upload of bug ';
55
56 type forced_bugfix_lookup is table of varchar2(1) index by binary_integer;
57
58 type data_contents is record
59 (download_mode varchar2(30),
60 last_definition_date date,
61 last_update_date date,
62 has_bugfix_replacement boolean,
63 pil_level integer,
64 has_files boolean,
65 download_code varchar2(30));
66
67 type upload_controller is record
68 (upload_bugfix_replacement boolean,
69 upload_prereqs_includes_links boolean,
70 upload_files boolean);
71
72 type row_counts is record
73 (bugfixes number,
74 bugfix_relationships number,
75 files number,
76 file_versions number,
77 bugfix_file_versions number);
78
79 type ums_table is record
80 (owner_name varchar2(30),
81 table_name varchar2(30),
82 last_analyzed date,
83 num_rows number,
84 delta_num_rows number);
85
86 type ums_tables is table of ums_table index by binary_integer;
87
88 g_uc upload_controller;
89 g_rc row_counts;
90 g_forced_bugfixes forced_bugfix_lookup;
91
92 --------------------------------------------------------------------------------
93 procedure debug(p_debug in varchar2)
94 is
95 begin
96 if (g_debug_flag <> DEBUG_OFF) then
97 fnd_file.put_line(fnd_file.Log, p_debug);
98 end if;
99 exception
100 when others then
101 null;
102 end debug;
103
104 --------------------------------------------------------------------------------
105 procedure debug(p_func_name in varchar2,
106 p_debug in varchar2)
107 is
108 begin
109 if (g_debug_flag <> DEBUG_OFF) then
110 fnd_file.put_line
111 (fnd_file.Log,
112 'FUNCTION:' || p_func_name || g_newline ||
113 'DEBUG :' || p_debug || g_newline ||
114 'SYSDATE :' || To_char(Sysdate, G_STD_DATE_MASK));
115 end if;
116 exception
117 when others then
118 null;
119 end debug;
120
121 --------------------------------------------------------------------------------
122 procedure set_debugging(p_debug_flag in varchar2)
123 is
124 l_old_debug_flag varchar2(10);
125 begin
126 l_old_debug_flag := g_debug_flag;
127 g_debug_flag := Nvl(Upper(Substr(p_debug_flag, 1, 1)), DEBUG_OFF);
128 if ((l_old_debug_flag = DEBUG_OFF) and (g_debug_flag = DEBUG_ON)) then
129 debug(' ');
130 debug('Update Management System Loader Debugger');
131 debug(rpad('-', 77, '-'));
132 debug('Sysdate = ' || to_char(sysdate, G_STD_DATE_MASK));
133 debug('Legend: DM: Download Mode. LDD: Last definition Date. LUD: Last Update Date.');
134 debug(' ');
135 end if;
136 exception
137 when others then
138 null;
139 end set_debugging;
140
141 --------------------------------------------------------------------------------
142 -- Locks the entity before insert.
143 -- p_entity_name - name of the entity
144 -- p_key1..3 - primary keys of the entity
145 --------------------------------------------------------------------------------
146 PROCEDURE lock_entity(p_entity_name in varchar2,
147 p_key1 in varchar2 default null,
148 p_key2 in varchar2 default null,
149 p_key3 in varchar2 default null)
150 is
151 l_entity varchar2(32000);
152 l_hash_value number;
153 l_lock_name varchar2(128);
154 l_lock_handle varchar2(128);
155 l_lock_status integer;
156 begin
157 -- Get a unique lock name
158
159 l_entity := 'FND.UMS.' || p_entity_name || '.' ||
160 p_key1 || '.' || p_key2 || '.' || p_key3;
161
162 if (lengthb(l_entity) > 128) then
163 -- lockname cannot be longer than 128 bytes.
164 -- Get a hash value between 1 and 65536.
165 l_hash_value := dbms_utility.get_hash_value(l_entity, 1, 65536);
166 l_lock_name := 'FND.UMS.HASH.' || p_entity_name || '.' || l_hash_value;
167 l_lock_name := substrb(l_lock_name, 1, 128);
168 else
169 l_lock_name := l_entity;
170 end if;
171
172 dbms_lock.allocate_unique(lockname => l_lock_name,
173 lockhandle => l_lock_handle,
174 expiration_secs => g_lock_lifetime);
175
176 l_lock_status := dbms_lock.request(lockhandle => l_lock_handle,
177 lockmode => dbms_lock.x_mode,
178 timeout => g_lock_waittime,
179 release_on_commit => TRUE);
180
181 if (l_lock_status <> 0) then
182 raise_application_error(ERROR_UNABLE_TO_LOCK,
183 'Unable to lock entity : ' || l_entity ||
184 '. dbms_lock.request(' || l_lock_name ||
185 ') returned : ' || l_lock_status);
186 end if;
187 end lock_entity;
188
189 --------------------------------------------------------------------------------
190 -- Analyzes table stats iff
191 -- - analyze hasn't been run for more than TABLE_ANALYZE_PERIOD of time
192 -- - percentage of row count change is more than TABLE_ANALYZE_PERCENTAGE
193 --
194 -- p_ums_table - ums table details
195 --------------------------------------------------------------------------------
196 procedure analyze_table(p_ums_table in ums_table)
197 is
198 l_analyze_needed boolean;
199 begin
200 if (g_debug_flag = DEBUG_STATS) then
201 debug('Owner.Table Name : ' || p_ums_table.owner_name || '.' || p_ums_table.table_name);
202 debug('Number of Data Changes : ' || p_ums_table.delta_num_rows);
203 debug('Last Analyzed Row Count : ' || p_ums_table.num_rows);
204 debug('Last Analyzed Date : ' || to_char(p_ums_table.last_analyzed, G_STD_DATE_MASK));
205 end if;
206
207 l_analyze_needed := false;
208
209 if ((p_ums_table.last_analyzed is null) or
210 (sysdate - p_ums_table.last_analyzed > TABLE_ANALYZE_PERIOD)) then
211
212 if (g_debug_flag = DEBUG_STATS) then
213 debug('Table has not been analyzed for more than TABLE_ANALYZE_PERIOD of ' ||
214 TABLE_ANALYZE_PERIOD || ' days');
215 end if;
216
217 l_analyze_needed := true;
218
219 elsif ((p_ums_table.num_rows is null) or
220 (p_ums_table.delta_num_rows > p_ums_table.num_rows * TABLE_ANALYZE_PERCENTAGE / 100)) then
221
222 if (g_debug_flag = DEBUG_STATS) then
223 debug(p_ums_table.delta_num_rows || ' data changes exceeds the TABLE_ANALYZE_PERCENTAGE of ' ||
224 TABLE_ANALYZE_PERCENTAGE || '%');
225 end if;
226
227 l_analyze_needed := true;
228
229 end if;
230
231 if (l_analyze_needed) then
232
233 fnd_stats.gather_table_stats(p_ums_table.owner_name, p_ums_table.table_name);
234
235 if (g_debug_flag = DEBUG_STATS) then
236 debug('Statistics were successfully gathered.');
237 debug(' ');
238 end if;
239
240 else
241 if (g_debug_flag = DEBUG_STATS) then
242 debug('There is no need to gather statistics.');
243 debug(' ');
244 end if;
245 end if;
246
247 exception
248 when others then
249 if (g_debug_flag = DEBUG_STATS) then
250 debug('analyze_table(''' ||
251 p_ums_table.owner_name || ''', ''' || p_ums_table.table_name || ''') failed.');
252 debug('SQLERRM : ' || sqlerrm);
253 end if;
254 end analyze_table;
255
256 --------------------------------------------------------------------------------
257 -- Gets UMS table details from dba_tables.
258 --------------------------------------------------------------------------------
259 procedure add_table_details(px_ums_tables in out nocopy ums_tables,
260 px_ums_table_count in out nocopy binary_integer,
261 p_table_name in varchar2,
262 p_delta_num_rows in number)
263 is
264 cursor l_applsys_schemas is
265 select fou.oracle_username
266 from fnd_oracle_userid fou,
267 fnd_product_installations fpi
268 where fou.oracle_id = fpi.oracle_id
269 and fpi.application_id = 0;
270
271 cursor l_ums_tables(p_owner in varchar2, p_table_name in varchar2) is
272 select owner, table_name, last_analyzed, num_rows
273 from dba_tables
274 where owner = p_owner
275 and table_name = p_table_name;
276 begin
277 for l_applsys_schema in l_applsys_schemas loop
278 for l_ums_table in l_ums_tables(l_applsys_schema.oracle_username, p_table_name) loop
279
280 px_ums_tables(px_ums_table_count).owner_name := l_ums_table.owner;
281 px_ums_tables(px_ums_table_count).table_name := l_ums_table.table_name;
282 px_ums_tables(px_ums_table_count).last_analyzed := l_ums_table.last_analyzed;
283 px_ums_tables(px_ums_table_count).num_rows := l_ums_table.num_rows;
284 px_ums_tables(px_ums_table_count).delta_num_rows := p_delta_num_rows;
285
286 px_ums_table_count := px_ums_table_count + 1;
287
288 end loop;
289 end loop;
290 exception
291 when others then
292 if (g_debug_flag = DEBUG_STATS) then
293 debug('Unable to get table details for ' || p_table_name || '.');
294 debug('SQLERRM : ' || sqlerrm);
295 end if;
296 end add_table_details;
297
298 --------------------------------------------------------------------------------
299 -- Analyzes UMS table stats
300 --------------------------------------------------------------------------------
301 procedure analyze_ums_tables
302 is
303 l_ums_tables ums_tables;
304 l_ums_table_count binary_integer;
305 l_debug_flag varchar2(10);
306 begin
307 l_debug_flag := g_debug_flag;
308 g_debug_flag := DEBUG_STATS;
309
310 l_ums_table_count := 0;
311
312 add_table_details(l_ums_tables, l_ums_table_count, 'FND_UMS_BUGFIXES', g_rc.bugfixes);
313 add_table_details(l_ums_tables, l_ums_table_count, 'FND_UMS_BUGFIX_RELATIONSHIPS', g_rc.bugfix_relationships);
314 add_table_details(l_ums_tables, l_ums_table_count, 'FND_UMS_FILES', g_rc.files);
315 add_table_details(l_ums_tables, l_ums_table_count, 'FND_UMS_FILE_VERSIONS', g_rc.file_versions);
316 add_table_details(l_ums_tables, l_ums_table_count, 'FND_UMS_BUGFIX_FILE_VERSIONS', g_rc.bugfix_file_versions);
317
318 if (g_debug_flag = DEBUG_STATS) then
319 debug(' ');
320 debug('Gathering Statistics for ' || l_ums_table_count || ' UMS table(s):');
321 debug(rpad('-', 50, '-'));
322 end if;
323
324 for i in 0 .. l_ums_table_count - 1 loop
325 analyze_table(l_ums_tables(i));
326 end loop;
327
328 g_debug_flag := l_debug_flag;
329 exception
330 when others then
331 g_debug_flag := l_debug_flag;
332 null;
333 end analyze_ums_tables;
334
335 ------------------------------------------------------------------------
336 -- Maps download_mode to data_contents.
337 ------------------------------------------------------------------------
338 function get_data_contents(p_download_mode in varchar2,
339 p_last_definition_date in date,
340 p_last_update_date in date)
341 return data_contents
342 is
343 l_data_contents data_contents;
344 begin
345 l_data_contents.download_mode := p_download_mode;
346 l_data_contents.last_definition_date := p_last_definition_date;
347 l_data_contents.last_update_date := p_last_update_date;
348
349 l_data_contents.has_bugfix_replacement := false;
350 l_data_contents.pil_level := PIL_LEVEL_NONE;
351 l_data_contents.has_files := false;
352 l_data_contents.download_code := '';
353
357 elsif (p_download_mode = DL_MODE_FILES_ONLY) then
354 if (p_download_mode = DL_MODE_NONE) then
355 l_data_contents.download_code := '';
356
358 l_data_contents.download_code := 'F';
359
360 l_data_contents.has_files := true;
361
362 elsif (p_download_mode = DL_MODE_REPLACEMENTS_ONLY) then
363 l_data_contents.download_code := 'BR';
364 l_data_contents.has_bugfix_replacement := true;
365
366 elsif (p_download_mode = DL_MODE_REPLACEMENTS_FILES) then
367 l_data_contents.download_code := 'BRF';
368 l_data_contents.has_bugfix_replacement := true;
369
370 l_data_contents.has_files := true;
371
372 elsif (p_download_mode = DL_MODE_PREREQS_ONLY) then
373 l_data_contents.download_code := 'BRP';
374 l_data_contents.has_bugfix_replacement := true;
375
376 l_data_contents.pil_level := PIL_LEVEL_PREREQS_INCLUDES;
377
378 elsif (p_download_mode = DL_MODE_PREREQS_FILES) then
379 l_data_contents.download_code := 'BRPF';
380 l_data_contents.has_bugfix_replacement := true;
381
382 l_data_contents.pil_level := PIL_LEVEL_PREREQS_INCLUDES;
383 l_data_contents.has_files := true;
384
385 elsif (p_download_mode = DL_MODE_LINKS_ONLY) then
386 l_data_contents.download_code := 'BRPL';
387 l_data_contents.has_bugfix_replacement := true;
388
389 l_data_contents.pil_level := PIL_LEVEL_LINKS;
390
391 elsif (p_download_mode = DL_MODE_LINKS_FILES) then
392 l_data_contents.download_code := 'BRPLF';
393 l_data_contents.has_bugfix_replacement := true;
394
395 l_data_contents.pil_level := PIL_LEVEL_LINKS;
396 l_data_contents.has_files := true;
397
398 else
399 raise_application_error(ERROR_UNKNOWN_DOWNLOAD_MODE,
400 'Unknown DOWNLOAD_MODE: ' || p_download_mode);
401
402 end if;
403
404 return l_data_contents;
405 end get_data_contents;
406
407 ------------------------------------------------------------------------
408 -- Drives download_mode from the has_ flags.
409 ------------------------------------------------------------------------
410 procedure derive_download_mode(px_data_contents in out nocopy data_contents)
411 is
412 l_download_mode varchar2(30);
413 l_download_code varchar2(30);
414 begin
415 l_download_mode := DL_MODE_NONE;
416 l_download_code := '';
417
418 if (px_data_contents.has_bugfix_replacement) then
419 l_download_mode := DL_MODE_REPLACEMENTS_ONLY;
420 l_download_code := 'BR';
421
422 if (px_data_contents.pil_level = PIL_LEVEL_PREREQS_INCLUDES) then
423 l_download_mode := DL_MODE_PREREQS_ONLY;
424 l_download_code := 'BRP';
425
426 if (px_data_contents.has_files) then
427 l_download_mode := DL_MODE_PREREQS_FILES;
428 l_download_code := 'BRPF';
429 end if;
430
431 elsif (px_data_contents.pil_level = PIL_LEVEL_LINKS) then
432 l_download_mode := DL_MODE_LINKS_ONLY;
433 l_download_code := 'BRPL';
434
435 if (px_data_contents.has_files) then
436 l_download_mode := DL_MODE_LINKS_FILES;
437 l_download_code := 'BRPLF';
438 end if;
439
440 elsif (px_data_contents.has_files) then
441 l_download_mode := DL_MODE_REPLACEMENTS_FILES;
442 l_download_code := 'BRF';
443 end if;
444
445 elsif (px_data_contents.has_files) then
446 l_download_mode := DL_MODE_FILES_ONLY;
447 l_download_code := 'F';
448 end if;
449
450 px_data_contents.download_mode := l_download_mode;
451 px_data_contents.download_code := l_download_code;
452
453 end derive_download_mode;
454
455 procedure debug_up_fnd_ums_bugfix
456 (p_release_name in varchar2,
457 p_bug_number in varchar2,
458 l_forced in boolean,
459 l_file_dc in data_contents,
460 l_forced_db_dc in data_contents,
461 l_db_dc in data_contents,
462 l_final_dc in data_contents)
463 is
464 l_debug varchar2(32000);
465 begin
466
467 if (g_debug_flag = DEBUG_ON) then
468 l_debug := 'Release Name: ' || p_release_name || ', ' ||
469 'Bug Number: ' || p_bug_number;
470
471 if (l_forced) then
472 l_debug := l_debug || ' CUSTOM_MODE = FORCE';
473 end if;
474
475 debug(l_debug);
476
477 -- Print Download Mode details.
478
479 l_debug := ' ' || rpad('File DM:', 10) ||
480 rpad(l_file_dc.download_mode || '(' || l_file_dc.download_code || ')', 24);
481
482 l_debug := l_debug || ' ' || rpad('DB DM:', 8) ||
483 rpad(l_db_dc.download_mode || '(' || l_db_dc.download_code || ')', 24);
484
485 if (l_forced) then
486 l_debug := l_debug || ' <- ' || rpad('Old DB DM:', 12) ||
487 rpad(l_forced_db_dc.download_mode || '(' || l_forced_db_dc.download_code || ')', 24);
488 end if;
489
490 debug(l_debug);
491
492 -- Print Last Definition Date details.
493
494 l_debug := ' ' || rpad('File LDD:', 10) ||
495 rpad(to_char(l_file_dc.last_definition_date, G_STD_DATE_MASK), 24);
496
497 if (l_file_dc.last_definition_date > l_db_dc.last_definition_date) then
498 l_debug := l_debug || ' > ';
499 elsif (l_file_dc.last_definition_date = l_db_dc.last_definition_date) then
500 l_debug := l_debug || ' = ';
501 else
502 l_debug := l_debug || ' < ';
503 end if;
504
505 l_debug := l_debug || rpad('DB LDD:', 8) ||
509 l_debug := l_debug || ' <- ' || rpad('Old DB LDD:', 12) ||
506 rpad(to_char(l_db_dc.last_definition_date, G_STD_DATE_MASK), 24);
507
508 if (l_forced) then
510 rpad(to_char(l_forced_db_dc.last_definition_date, G_STD_DATE_MASK), 24);
511 end if;
512
513 debug(l_debug);
514
515 -- Print Last Update Date details.
516
517 l_debug := ' ' || rpad('File LUD:', 10) ||
518 rpad(to_char(l_file_dc.last_update_date, G_STD_DATE_MASK), 24);
519
520 if (l_file_dc.last_update_date > l_db_dc.last_update_date) then
521 l_debug := l_debug || ' > ';
522 elsif (l_file_dc.last_update_date = l_db_dc.last_update_date) then
523 l_debug := l_debug || ' = ';
524 else
525 l_debug := l_debug || ' < ';
526 end if;
527
528 l_debug := l_debug || rpad('DB LUD:', 8) ||
529 rpad(to_char(l_db_dc.last_update_date, G_STD_DATE_MASK), 24);
530
531 if (l_forced) then
532 l_debug := l_debug || ' <- ' || rpad('Old DB LUD:', 12) ||
533 rpad(to_char(l_forced_db_dc.last_update_date, G_STD_DATE_MASK), 24);
534 end if;
535
536 debug(l_debug);
537 debug(rpad(' ', 80, '-'));
538
539 -- Print the upload flags.
540
541 if (g_uc.upload_bugfix_replacement) then
542 l_debug := ' up_bugfix_replacement: Y, ';
543 else
544 l_debug := ' up_bugfix_replacement: N, ';
545 end if;
546
547 if (g_uc.upload_prereqs_includes_links) then
548 l_debug := l_debug || 'up_prereqs_includes_links: Y, ';
549 else
550 l_debug := l_debug || 'up_prereqs_includes_links: N, ';
551 end if;
552
553 if (g_uc.upload_files) then
554 l_debug := l_debug || 'up_files: Y';
555 else
556 l_debug := l_debug || 'up_files: N';
557 end if;
558
559 debug(l_debug);
560
561 -- Report final result.
562
563 l_debug := ' ' || rpad('Final DM:', 10) ||
564 rpad(l_final_dc.download_mode || '(' || l_final_dc.download_code || ')', 24);
565
566 debug(l_debug);
567
568 l_debug := ' ' || rpad('Final LDD:',10) ||
569 rpad(to_char(l_final_dc.last_definition_date, G_STD_DATE_MASK), 24);
570
571 debug(l_debug);
572
573 l_debug := ' ' || rpad('Final LUD:',10) ||
574 rpad(to_char(l_final_dc.last_update_date, G_STD_DATE_MASK), 24);
575
576 debug(l_debug);
577
578 debug(' ');
579 end if;
580
581 end debug_up_fnd_ums_bugfix;
582
583 --------------------------------------------------------------------------------
584 procedure up_fnd_ums_bugfix
585 (p_upload_phase in varchar2,
586 p_release_name in varchar2,
587 p_baseline in varchar2,
588 p_bug_number in varchar2,
589 p_download_mode in varchar2,
590 p_application_short_name in varchar2,
591 p_release_status in varchar2,
592 p_type in varchar2,
593 p_abstract in varchar2,
594 p_last_definition_date in varchar2,
595 p_last_update_date in varchar2,
596 p_custom_mode in varchar2)
597 is
598 l_file_dc data_contents;
599 l_db_dc data_contents;
600 l_final_dc data_contents;
601 -- rjaiswal
602 l_baseline varchar2(150);
603 l_forced boolean;
604 l_forced_db_dc data_contents;
605 begin
606 -- rjaiswal
607 select decode(instr(p_baseline, '.',1,2) - 1, -1,p_baseline ,
608 substr(p_baseline , instr(p_baseline , '.',1,2) +1)) into l_baseline from dual;
609 if (p_upload_phase = 'BEGIN') then
610 -- Lock the entity first
611
612 lock_entity('FND_UMS_BUGFIXES', 'TOP_LEVEL', p_release_name, p_bug_number);
613
614 -- Gather LDT file details
615
616 l_file_dc := get_data_contents(p_download_mode,
617 to_date(nvl(p_last_definition_date, p_last_update_date), G_STD_DATE_MASK),
618 to_date(p_last_update_date, G_STD_DATE_MASK));
619
620 -- Gather database details
621
622 declare
623 l_bugfix fnd_ums_bugfixes%ROWTYPE;
624 begin
625 select /*+ INDEX(fnd_ums_bugfixes fnd_ums_bugfixes_u2) */ *
626 into l_bugfix
627 from fnd_ums_bugfixes
628 where release_name = p_release_name
629 and bug_number = p_bug_number
630 and baseline = l_baseline;
631
632
633 g_bugfix_guid := l_bugfix.bugfix_guid;
634
635 l_db_dc := get_data_contents(l_bugfix.download_mode,
636 l_bugfix.last_definition_date,
637 l_bugfix.last_update_date);
638 exception
639 when no_data_found then
640 -- there is no data for this bugfix in the database
641
642 g_bugfix_guid := sys_guid();
643
644 l_db_dc := get_data_contents(DL_MODE_NONE,
645 g_default_date,
646 g_default_date);
647 end;
648
649 -- determine if any upload is allowed.
650
651 g_uc.upload_bugfix_replacement := false;
652 g_uc.upload_prereqs_includes_links := false;
653 g_uc.upload_files := false;
654
655 l_forced := false;
656 if (p_custom_mode = 'FORCE') then
657 -- If the bugfix is not already FORCEd, then FORCE it to be re-uploaded.
658
659 declare
660 l_already_forced boolean;
661 begin
662 declare
666 -- fetching the entry from that index raises no_data_found exception.
663 l_vc2 varchar2(1);
664 begin
665 -- if a collection doesn't have an entry at a given index then
667
668 l_vc2 := g_forced_bugfixes(p_bug_number);
669 l_already_forced := true;
670 exception
671 when no_data_found then
672 l_already_forced := false;
673 end;
674
675 if (not l_already_forced) then
676 -- Force bugfix to be re-uploaded.
677 l_forced := true;
678
679 l_forced_db_dc := l_db_dc;
680
681 g_uc.upload_bugfix_replacement := true;
682 g_uc.upload_prereqs_includes_links := true;
683 g_uc.upload_files := true;
684
685 l_db_dc := get_data_contents(DL_MODE_NONE,
686 g_default_date,
687 g_default_date);
688
689 -- Mark this bugfix as forced.
690
691 g_forced_bugfixes(p_bug_number) := 'Y';
692 end if;
693 end;
694 end if;
695
696 l_final_dc := l_db_dc;
697
698 -- Decide whether or not bugfix and replacement (BR) should be uploaded
699 --
700 -- | DB
701 -- | no_data_found files_only bugfix_replacement_exists
702 -- ---------+------------------------------------------------------
703 -- files | delete delete do nothing
704 -- F only | insert insert
705 -- i |
706 -- l BR | delete delete file.LUD <= db.LUD : do nothing
707 -- e exists | insert insert file.LUD > db.LUD : delete/insert
708 --
709
710 if (l_file_dc.has_bugfix_replacement) then
711 -- File has bugfix and replacement data
712
713 l_final_dc.has_bugfix_replacement := true;
714
715 if (l_db_dc.has_bugfix_replacement) then
716 -- DB has bugfix and replacement data
717
718 if (l_file_dc.last_update_date <= l_db_dc.last_update_date) then
719 -- File last update date is older or same, do nothing
720
721 null;
722
723 else
724 -- File update date is newer
725
726 l_final_dc.last_update_date := l_file_dc.last_update_date;
727
728 g_uc.upload_bugfix_replacement := true;
729
730 end if; -- LUD
731 else
732 l_final_dc.last_update_date := l_file_dc.last_update_date;
733
734 g_uc.upload_bugfix_replacement := true;
735
736 end if;
737
738 else
739 if (l_db_dc.has_bugfix_replacement) then
740 -- DB has bugfix and replacement data, do nothing
741 null;
742
743 else
744 -- Create the dummy/template bugfix definition for foreign
745 -- key reference purposes.
746
747 g_uc.upload_bugfix_replacement := true;
748
749 l_final_dc.has_bugfix_replacement := false;
750 end if;
751 end if;
752
753 -- Decide whether or not PIL (prereqs/includes/links) data should be uploaded
754
755 if (l_file_dc.pil_level > PIL_LEVEL_NONE) then
756 -- File has PIL data
757
758 if (l_db_dc.pil_level > PIL_LEVEL_NONE) then
759 -- DB has PIL data
760
761 if (l_file_dc.last_definition_date < l_db_dc.last_definition_date) then
762 -- File last definition date is older, do nothing
763 null;
764
765 elsif (l_file_dc.last_definition_date = l_db_dc.last_definition_date) then
766 -- File and DB last definition dates are same, upload the missing
767 -- definition data. Links might be missing.
768 --
769 -- | DB |
770 -- | P L |
771 -- ------+-----+------------------------------
772 -- F P | P L | <- 1st row : Final result
773 -- i | | <- 2nd row : What is uploaded
774 -- l L | L L |
775 -- e | L |
776 --
777 --
778
779 if (l_file_dc.pil_level > l_db_dc.pil_level) then
780 -- Upload the link information
781
782 l_final_dc.last_definition_date := l_file_dc.last_definition_date;
783
784 g_uc.upload_prereqs_includes_links := true;
785
786 l_final_dc.pil_level := l_file_dc.pil_level;
787 end if;
788
789 else
790 -- File definition date is newer
791
792 l_final_dc.last_definition_date := l_file_dc.last_definition_date;
793
794 g_uc.upload_prereqs_includes_links := true;
795
796 l_final_dc.pil_level := l_file_dc.pil_level;
797 end if; -- LDD
798
799 else
800 -- DB has no definition, insert data from file
801
802 l_final_dc.last_definition_date := l_file_dc.last_definition_date;
803
804 g_uc.upload_prereqs_includes_links := true;
805
806 l_final_dc.pil_level := l_file_dc.pil_level;
807 end if; -- l_db_dc.pil_level
808
809 else
810 -- File has no PIL data, do nothing
811 null;
812
813 end if; -- l_file_dc.pil_level
814
818 -- | no_files files exist
815 -- Decide whether or not files should be uploaded
816 --
817 -- | DB
819 -- ---------+--------------------------------
820 -- F no | do nothing do nothing
821 -- i files |
822 -- l |
823 -- e files | delete file.LDD <= db.LDD : do nothing
824 -- exist | insert file.LDD > db.LDD : delete/insert
825 --
826
827 if (l_file_dc.has_files) then
828 -- File has files data
829
830 l_final_dc.has_files := true;
831
832 if (l_db_dc.has_files) then
833 -- DB has files data
834
835 if (l_file_dc.last_definition_date <= l_db_dc.last_definition_date) then
836 -- file.LDD is older than DB.LDD or they are same, do nothing
837
838 null;
839
840 else
841 -- file.LDD is newer than DB.LDD
842 -- Do not change the final.LDD. In ARU LDD reflects changes in dependency
843 -- tree and file contents. New f<bug_number>.ldt file only has file
844 -- contents and the LDD in this file does not reflect the correct LDD
845 -- of the bugfix dependency information.
846
847 g_uc.upload_files := true;
848
849 end if;
850
851 else
852 -- DB has no files data
853
854 g_uc.upload_files := true;
855
856 end if;
857
858 else
859 -- File has no files data, do nothing
860
861 null;
862
863 end if;
864
865 -- Derive Final Download Mode
866
867 derive_download_mode(l_final_dc);
868
869 -- Debug
870
871 if (g_debug_flag = DEBUG_ON) then
872 debug_up_fnd_ums_bugfix(p_release_name,
873 p_bug_number,
874 l_forced,
875 l_file_dc,
876 l_forced_db_dc,
877 l_db_dc,
878 l_final_dc);
879 end if;
880
881 --
882 -- Abort the upload if there is nothing new to upload.
883 -- Disabled until FNDLOAD implements the abort logic.
884 --
885 if (g_debug_flag = DEBUG_ABORT) then
886 if (g_uc.upload_bugfix_replacement or
887 g_uc.upload_prereqs_includes_links or
888 g_uc.upload_files) then
889 null;
890
891 else
892 if ((l_file_dc.last_update_date < l_db_dc.last_update_date) and
893 (l_file_dc.last_definition_date < l_db_dc.last_definition_date)) then
894 -- File is older and has no more data than DB.
895
896 raise_application_error(ERROR_ABORT,
897 MSG_ABORT_OLDER || p_bug_number);
898 else
899 -- Dates are same and file has no more data than DB.
900
901 raise_application_error(ERROR_ABORT,
902 MSG_ABORT_LEVEL || p_bug_number);
903 end if;
904 end if;
905 end if;
906
907 -- Real UPLOAD ...
908
909 -- Delete children ...
910
911 if (g_uc.upload_files) then
912 -- delete files
913
914 delete from fnd_ums_bugfix_file_versions
915 where bugfix_guid = g_bugfix_guid;
916
917 g_rc.bugfix_file_versions := g_rc.bugfix_file_versions + sql%rowcount;
918 end if;
919
920 if (g_uc.upload_prereqs_includes_links) then
921 -- delete prereqs, includes
922
923 delete from fnd_ums_bugfix_relationships
924 where bugfix_guid = g_bugfix_guid
925 and relation_type in (REL_TYPE_PREREQS,
926 REL_TYPE_INDIRECTLY_PREREQS,
927 REL_TYPE_INCLUDES,
928 REL_TYPE_INDIRECTLY_INCLUDES);
929
930 g_rc.bugfix_relationships := g_rc.bugfix_relationships + sql%rowcount;
931 end if;
932
933 -- Insert the bugfix data if necessary
934
935 if (g_uc.upload_bugfix_replacement) then
936 -- delete replacement
937
938 delete from fnd_ums_bugfix_relationships
939 where bugfix_guid = g_bugfix_guid
940 and relation_type = REL_TYPE_REPLACED_BY;
941
942 g_rc.bugfix_relationships := g_rc.bugfix_relationships + sql%rowcount;
943
944 -- delete bugfix
945
946 delete /*+ INDEX(fnd_ums_bugfixes fnd_ums_bugfixes_u1) */
947 from fnd_ums_bugfixes
948 where bugfix_guid = g_bugfix_guid;
949
950 g_rc.bugfixes := g_rc.bugfixes + sql%rowcount;
951
952 -- insert bugfix
953
954 declare
955 l_application_short_name varchar2(32000);
956 l_release_status varchar2(32000);
957 l_type varchar2(32000);
958 l_abstract varchar2(32000);
959 begin
960 if (l_file_dc.download_mode = DL_MODE_FILES_ONLY) then
961 l_application_short_name := nvl(p_application_short_name, NOT_AVAILABLE);
962 l_release_status := nvl(p_release_status, NOT_AVAILABLE);
963 l_type := nvl(p_type, NOT_AVAILABLE);
964 l_abstract := nvl(p_abstract, NOT_AVAILABLE);
965 else
966 l_application_short_name := p_application_short_name;
970 end if;
967 l_release_status := p_release_status;
968 l_type := p_type;
969 l_abstract := p_abstract;
971
972 insert into fnd_ums_bugfixes
973 (bugfix_guid,
974 release_name,
975 bug_number,
976 baseline,
977 download_mode,
978 application_short_name,
979 release_status,
980 type,
981 abstract,
982 last_definition_date,
983 last_update_date)
984 values
985 (g_bugfix_guid,
986 p_release_name,
987 p_bug_number,
988 l_baseline,
989 l_final_dc.download_mode,
990 l_application_short_name,
991 l_release_status,
992 l_type,
993 l_abstract,
994 l_final_dc.last_definition_date,
995 l_final_dc.last_update_date);
996
997 g_rc.bugfixes := g_rc.bugfixes + sql%rowcount;
998 end;
999 elsif (g_uc.upload_prereqs_includes_links or g_uc.upload_files) then
1000 -- If PIL (affects DM and LDD) or Files (affects DM) are uploaded,
1001 -- but bugfix and replacement data is not uploaded
1002 -- then download_mode and last_definition_date should be updated.
1003 -- For Example: If database is RO, and ldt is LF, and dates are same
1004 -- then PIL and Files will be uploaded but bugfix and replacement
1005 -- data will not.
1006
1007 update /*+ INDEX(fnd_ums_bugfixes fnd_ums_bugfixes_u1) */ fnd_ums_bugfixes
1008 set download_mode = l_final_dc.download_mode,
1009 last_definition_date = l_final_dc.last_definition_date
1010 where bugfix_guid = g_bugfix_guid;
1011
1012 g_rc.bugfixes := g_rc.bugfixes + sql%rowcount;
1013 end if;
1014
1015 elsif(p_upload_phase = 'END') then
1016 -- no work to do
1017 null;
1018 else
1019 raise_application_error(ERROR_UNKNOWN_UPLOAD_PHASE,
1020 'Unknown UPLOAD_PHASE: ' || p_upload_phase);
1021 end if;
1022
1023 -- do not catch exceptions here.
1024 end up_fnd_ums_bugfix;
1025
1026 --------------------------------------------------------------------------------
1027 function new_file_guid_at
1028 (p_application_short_name in fnd_ums_files.application_short_name%type,
1029 p_location in fnd_ums_files.location%type,
1030 p_name in fnd_ums_files.name%type)
1031 return raw
1032 is
1033 pragma autonomous_transaction;
1034 l_file_guid fnd_ums_files.file_guid%type;
1035 begin
1036 -- lock the entity first
1037
1038 lock_entity('FND_UMS_FILES', p_application_short_name, p_location, p_name);
1039
1040 -- check the existence again
1041
1042 begin
1043 select /*+ INDEX(fnd_ums_files fnd_ums_files_u2) */ file_guid
1044 into l_file_guid
1045 from fnd_ums_files
1046 where application_short_name = p_application_short_name
1047 and location = p_location
1048 and name = p_name;
1049 exception
1050 when no_data_found then
1051 -- populate FND_UMS_FILES
1052
1053 insert into fnd_ums_files
1054 (file_guid,
1055 application_short_name,
1056 location,
1057 name)
1058 values
1059 (sys_guid(),
1060 p_application_short_name,
1061 p_location,
1062 p_name)
1063 returning file_guid
1064 into l_file_guid;
1065
1066 g_rc.files := g_rc.files + sql%rowcount;
1067 end;
1068
1069 commit;
1070
1071 return l_file_guid;
1072
1073 exception
1074 when others then
1075 rollback;
1076 raise;
1077 end new_file_guid_at;
1078
1079 --------------------------------------------------------------------------------
1080 function get_file_guid
1081 (p_application_short_name in fnd_ums_files.application_short_name%type,
1082 p_location in fnd_ums_files.location%type,
1083 p_name in fnd_ums_files.name%type)
1084 return raw
1085 is
1086 l_file_guid fnd_ums_files.file_guid%type;
1087 begin
1088 begin
1089 select /*+ INDEX(fnd_ums_files fnd_ums_files_u2) */ file_guid
1090 into l_file_guid
1091 from fnd_ums_files
1092 where application_short_name = p_application_short_name
1093 and location = p_location
1094 and name = p_name;
1095 exception
1096 when no_data_found then
1097 l_file_guid := new_file_guid_at(p_application_short_name,
1098 p_location,
1099 p_name);
1100 end;
1101
1102 return l_file_guid;
1103 end get_file_guid;
1104
1105 --------------------------------------------------------------------------------
1106 function new_file_version_guid_at
1107 (p_file_guid in fnd_ums_file_versions.file_guid%type,
1108 p_version in fnd_ums_file_versions.version%type)
1109 return raw
1110 is
1111 pragma autonomous_transaction;
1112 l_file_version_guid fnd_ums_file_versions.file_version_guid%type;
1113 begin
1114 -- lock the entity first
1115
1116 lock_entity('FND_UMS_FILE_VERSIONS', p_file_guid, p_version);
1117
1118 -- check the existence again
1119
1120 begin
1121 select /*+ INDEX(fnd_ums_file_versions fnd_ums_file_versions_u2) */ file_version_guid
1122 into l_file_version_guid
1123 from fnd_ums_file_versions
1124 where file_guid = p_file_guid
1128 -- populate FND_UMS_FILE_VERSIONS
1125 and version = p_version;
1126 exception
1127 when no_data_found then
1129
1130 insert into fnd_ums_file_versions
1131 (file_version_guid,
1132 file_guid,
1133 version)
1134 values
1135 (sys_guid(),
1136 p_file_guid,
1137 p_version)
1138 returning file_version_guid
1139 into l_file_version_guid;
1140
1141 g_rc.file_versions := g_rc.file_versions + sql%rowcount;
1142 end;
1143
1144 commit;
1145
1146 return l_file_version_guid;
1147
1148 exception
1149 when others then
1150 rollback;
1151 raise;
1152 end new_file_version_guid_at;
1153
1154 --------------------------------------------------------------------------------
1155 function get_file_version_guid
1156 (p_file_guid in fnd_ums_file_versions.file_guid%type,
1157 p_version in fnd_ums_file_versions.version%type)
1158 return raw
1159 is
1160 l_file_version_guid fnd_ums_file_versions.file_version_guid%type;
1161 begin
1162 begin
1163 select /*+ INDEX(fnd_ums_file_versions fnd_ums_file_versions_u2) */ file_version_guid
1164 into l_file_version_guid
1165 from fnd_ums_file_versions
1166 where file_guid = p_file_guid
1167 and version = p_version;
1168 exception
1169 when no_data_found then
1170 l_file_version_guid := new_file_version_guid_at(p_file_guid,
1171 p_version);
1172 end;
1173
1174 return l_file_version_guid;
1175 end get_file_version_guid;
1176
1177 --------------------------------------------------------------------------------
1178 procedure up_fnd_ums_bugfix_file
1179 (p_application_short_name in varchar2,
1180 p_location in varchar2,
1181 p_name in varchar2,
1182 p_version in varchar2)
1183 is
1184 l_file_guid fnd_ums_file_versions.file_guid%type;
1185 l_file_version_guid fnd_ums_file_versions.file_version_guid%type;
1186 begin
1187 if (g_uc.upload_files) then
1188 -- File upload is allowed.
1189
1190 -- Get file_guid
1191
1192 l_file_guid := get_file_guid(p_application_short_name,
1193 p_location,
1194 p_name);
1195
1196 -- Get file_version_guid
1197
1198 l_file_version_guid := get_file_version_guid(l_file_guid,
1199 p_version);
1200
1201 -- populate FND_UMS_BUGFIX_FILE_VERSIONS
1202
1203 insert into fnd_ums_bugfix_file_versions
1204 (bugfix_guid,
1205 file_version_guid)
1206 values
1207 (g_bugfix_guid,
1208 l_file_version_guid);
1209
1210 g_rc.bugfix_file_versions := g_rc.bugfix_file_versions + sql%rowcount;
1211 else
1212 -- File upload is not allowed.
1213 null;
1214 end if;
1215 end up_fnd_ums_bugfix_file;
1216
1217 --------------------------------------------------------------------------------
1218 function new_bugfix_guid_at
1219 (p_release_name in fnd_ums_bugfixes.release_name%type,
1220 p_bug_number in fnd_ums_bugfixes.bug_number%type)
1221 return raw
1222 is
1223 pragma autonomous_transaction;
1224 l_bugfix_guid fnd_ums_bugfixes.bugfix_guid%type;
1225 begin
1226 -- lock the entity first
1227
1228 lock_entity('FND_UMS_BUGFIXES', p_release_name, p_bug_number);
1229
1230 -- check the existence again
1231
1232 begin
1233 select /*+ INDEX(fnd_ums_bugfixes fnd_ums_bugfixes_u2) */ bugfix_guid
1234 into l_bugfix_guid
1235 from fnd_ums_bugfixes
1236 where release_name = p_release_name
1237 and bug_number = p_bug_number;
1238 exception
1239 when no_data_found then
1240 -- If bugfix doesn't exist then create a DL_MODE_NONE bugfix.
1241 -- This happens in these two cases.
1242 -- - p_download_mode is NONE.
1243 -- This happens when INCLUDES, INDIRECTLY_INCLUDES relationships
1244 -- are downloaded under the top level bugfix.
1245 -- - forward relationship references.
1246 -- If two entities refer to each other, the first entity will
1247 -- have a forward relationship reference to the second one.
1248 -- That second one may not exist in the DB, so a bugfix must
1249 -- be created to get the bugfix_guid. This bugfix is marked
1250 -- as DL_MODE_NONE so that its definition will be replaced
1251 -- with the real definition.
1252
1253 -- populate FND_UMS_BUGFIXES
1254
1255 insert into fnd_ums_bugfixes
1256 (bugfix_guid,
1257 release_name,
1258 bug_number,
1259 download_mode,
1260 application_short_name,
1261 release_status,
1262 type,
1263 abstract,
1264 last_definition_date,
1265 last_update_date)
1266 values
1267 (sys_guid(),
1268 p_release_name,
1269 p_bug_number,
1270 DL_MODE_NONE,
1271 NOT_AVAILABLE,
1272 NOT_AVAILABLE,
1273 NOT_AVAILABLE,
1274 NOT_AVAILABLE,
1275 g_default_date,
1276 g_default_date)
1277 returning bugfix_guid
1278 into l_bugfix_guid;
1279
1280 g_rc.bugfixes := g_rc.bugfixes + sql%rowcount;
1281 end;
1282
1283 commit;
1284
1285 return l_bugfix_guid;
1286
1287 exception
1291 end new_bugfix_guid_at;
1288 when others then
1289 rollback;
1290 raise;
1292
1293 --------------------------------------------------------------------------------
1294 function get_bugfix_guid
1295 (p_release_name in fnd_ums_bugfixes.release_name%type,
1296 p_bug_number in fnd_ums_bugfixes.bug_number%type)
1297 return raw
1298 is
1299 l_bugfix_guid fnd_ums_bugfixes.bugfix_guid%type;
1300 begin
1301 begin
1302 select /*+ INDEX(fnd_ums_bugfixes fnd_ums_bugfixes_u2) */ bugfix_guid
1303 into l_bugfix_guid
1304 from fnd_ums_bugfixes
1305 where release_name = p_release_name
1306 and bug_number = p_bug_number;
1307 exception
1308 when no_data_found then
1309 l_bugfix_guid := new_bugfix_guid_at(p_release_name,
1310 p_bug_number);
1311 end;
1312
1313 return l_bugfix_guid;
1314 end get_bugfix_guid;
1315
1316 --------------------------------------------------------------------------------
1317 procedure up_fnd_ums_bugfix_relationship
1318 (p_relation_type in varchar2,
1319 p_related_bugfix_release_name in varchar2,
1320 p_related_bugfix_bug_number in varchar2,
1321 p_related_bugfix_download_mode in varchar2)
1322 is
1323 l_related_bugfix_guid fnd_ums_bugfix_relationships.related_bugfix_guid%type;
1324 begin
1325 if (((g_uc.upload_prereqs_includes_links) and
1326 (p_relation_type in (REL_TYPE_PREREQS,
1327 REL_TYPE_INDIRECTLY_PREREQS,
1328 REL_TYPE_INCLUDES,
1329 REL_TYPE_INDIRECTLY_INCLUDES))) or
1330 ((g_uc.upload_bugfix_replacement) and
1331 (p_relation_type = REL_TYPE_REPLACED_BY))) then
1332
1333 -- Get the related_bugfix_guid
1334
1335 l_related_bugfix_guid := get_bugfix_guid(p_related_bugfix_release_name,
1336 p_related_bugfix_bug_number);
1337
1338 -- insert the relationship
1339
1340 insert into fnd_ums_bugfix_relationships
1341 (bugfix_guid,
1342 relation_type,
1343 related_bugfix_guid)
1344 values
1345 (g_bugfix_guid,
1346 p_relation_type,
1347 l_related_bugfix_guid);
1348
1349 g_rc.bugfix_relationships := g_rc.bugfix_relationships + sql%rowcount;
1350 else
1351 -- Either relationship upload is not allowed or
1352 -- REL_TYPE_REPLACES, REL_TYPE_REP_BY_FIRST_NON_OBS and unknown
1353 -- relationships are discarded.
1354 null;
1355 end if;
1356 end up_fnd_ums_bugfix_relationship;
1357
1358 --------------------------------------------------------------------------------
1359 procedure up_fnd_ums_one_bugfix
1360 (p_upload_phase in varchar2,
1361 p_release_name in varchar2,
1362 p_baseline in varchar2,
1363 p_bug_number in varchar2)
1364 is
1365 begin
1366 if (p_upload_phase = 'BEGIN') then
1367 analyze_ums_tables();
1368
1369 elsif (p_upload_phase = 'END') then
1370 -- no work to do
1371 null;
1372 else
1373 raise_application_error(ERROR_UNKNOWN_UPLOAD_PHASE,
1374 'Unknown UPLOAD_PHASE: ' || p_upload_phase);
1375 end if;
1376 end up_fnd_ums_one_bugfix;
1377
1378 --------------------------------------------------------------------------------
1379 procedure up_fnd_ums_bugfixes
1380 (p_upload_phase in varchar2,
1381 p_entity_download_mode in varchar2,
1382 p_release_name in varchar2,
1383 p_bug_number in varchar2,
1384 p_start_date in varchar2,
1385 p_end_date in varchar2)
1386 is
1387 begin
1388 if (p_upload_phase = 'BEGIN') then
1389 analyze_ums_tables();
1390
1391 elsif (p_upload_phase = 'END') then
1392 -- no work to do
1393 null;
1394 else
1395 raise_application_error(ERROR_UNKNOWN_UPLOAD_PHASE,
1396 'Unknown UPLOAD_PHASE: ' || p_upload_phase);
1397 end if;
1398 end up_fnd_ums_bugfixes;
1399
1400 --------------------------------------------------------------------------------
1401 function newline
1402 return varchar2
1403 is
1404 l_newline varchar2(100);
1405 l_plsql varchar2(2000);
1406 begin
1407 -- First try fnd_global.newline.
1408 begin
1409 --
1410 -- Use dynamic call not to have compile time dependency
1411 --
1412 l_plsql := 'begin :b_newline := fnd_global.newline(); end;';
1413 execute immediate l_plsql using out l_newline;
1414 exception
1415 when others then
1416 --
1417 -- Use dynamic call to go around the GSCC. chr() is not allowed.
1418 --
1419 l_plsql := 'begin ' ||
1420 ' :b_newline := convert(chr(10), ' ||
1421 ' substr(userenv(''LANGUAGE''), ' ||
1422 ' instr(userenv(''LANGUAGE''), ''.'') + 1), ' ||
1423 ' ''US7ASCII''); ' ||
1424 'end; ';
1425 execute immediate l_plsql using out l_newline;
1426 end;
1427
1428 return l_newline;
1429 end newline;
1430
1431 begin
1432 g_debug_flag := DEBUG_OFF;
1433 g_newline := fnd_ums_loader.newline();
1434
1435 g_default_date := to_date('1900/01/01 00:00:00', G_STD_DATE_MASK);
1436
1437 g_rc.bugfixes := 0;
1438 g_rc.bugfix_relationships := 0;
1439 g_rc.files := 0;
1440 g_rc.file_versions := 0;
1441 g_rc.bugfix_file_versions := 0;
1442
1443 end fnd_oam_ums_loader;