[Home] [Help]
PACKAGE BODY: APPS.EDW_COLLECTION_UTIL
Source
1 PACKAGE BODY edw_collection_util AS
2 /* $Header: EDWSRCTB.pls 120.0 2005/06/01 15:01:47 appldev noship $ */
3 version CONSTANT CHAR (80)
4 := '$Header: EDWSRCTB.pls 120.0 2005/06/01 15:01:47 appldev noship $';
5 g_source_link fnd_profile_option_values.profile_option_value%TYPE;
6 g_target_link fnd_profile_option_values.profile_option_value%TYPE;
7 g_source_same_as_target BOOLEAN;
8 g_debug BOOLEAN;
9 g_parallel PLS_INTEGER;
10 g_staging_table user_tables.table_name%TYPE;
11 g_rbs user_segments.segment_name%TYPE;
12 g_op_tablespace user_tablespaces.tablespace_name%TYPE;
13 g_bis_schema user_users.username%TYPE;
14 tab_stglist tablist_type;
15 g_transport_data BOOLEAN;
16
17
18 -- ------------------------------------------------------------------
19 -- Name: Setup
20 -- Desc: Initial function called to setup log file and push program.
21 -- p_object_name
22 -- ------------------------------------------------------------------
23 FUNCTION setup (p_object_name IN VARCHAR2) RETURN BOOLEAN IS
24 Begin
25 if setup(p_object_name,null,null,TRUE)=false then
26 return false;
27 end if;
28 return true;
29 EXCEPTION
30 WHEN OTHERS
31 THEN
32 edw_log.put_line (' Setup of '|| p_object_name|| ' failed. Error '||sqlerrm,FND_LOG.LEVEL_ERROR);
33 RETURN (FALSE);
34 END setup;
35
36 FUNCTION setup (
37 p_object_name IN VARCHAR2,
38 p_pk_view IN VARCHAR2,
39 p_missing_key_view IN VARCHAR2,
40 p_transport_data IN BOOLEAN
41 )
42 RETURN BOOLEAN
43 IS
44 l_dir VARCHAR2 (400);
45 l_option_value VARCHAR2 (60);
46 l_status BOOLEAN;
47 l_ap_status VARCHAR2 (30);
48 l_industry VARCHAR2 (30);
49 BEGIN
50 put_debug_msg ('********** Start Setup Function');
51
52 IF (fnd_installation.get_app_info (
53 'BIS',
54 l_ap_status,
55 l_industry,
56 g_bis_schema
57 )
58 )
59 THEN
60 IF (is_instance_enabled)
61 THEN
62 COMMIT;
63 l_status := TRUE;
64 put_debug_msg ('Reading Object Settings.');
65
66 IF edw_source_option.get_source_option (
67 p_object_name,
68 NULL,
69 'TRACE_SR',
70 l_option_value
71 )
72 THEN
73 IF l_option_value = 'Y'
74 THEN
75 null;
76 END IF;
77 ELSE
78 l_status := FALSE;
79 END IF;
80
81 IF edw_source_option.get_source_option (
82 p_object_name,
83 NULL,
84 'DEBUG_SR',
85 l_option_value
86 )
87 THEN
88 IF l_option_value = 'Y'
89 THEN
90 g_debug := TRUE;
91 ELSE
92 g_debug := FALSE;
93 END IF;
94 ELSE
95 l_status := FALSE;
96 END IF;
97
98 IF edw_source_option.get_source_option (
99 p_object_name,
100 NULL,
101 'PARALLELISM_SR',
102 l_option_value
103 )
104 THEN
105 g_parallel := NVL (l_option_value, 0);
106 ELSE
107 l_status := FALSE;
108 END IF;
109
110 IF edw_source_option.get_source_option (
111 p_object_name,
112 NULL,
113 'COMMITSIZE_SR',
114 l_option_value
115 )
116 THEN
117 g_push_size := NVL (l_option_value, 0);
118 ELSE
119 l_status := FALSE;
120 END IF;
121
122 IF edw_source_option.get_source_option (
123 p_object_name,
124 NULL,
125 'ROLLBACK_SR',
126 l_option_value
127 )
128 THEN
129 g_rbs := l_option_value;
130 ELSE
131 l_status := FALSE;
132 END IF;
133
134 IF edw_source_option.get_source_option (
135 p_object_name,
136 NULL,
137 'OPTABLESPACE_SR',
138 l_option_value
139 )
140 THEN
141 g_op_tablespace := l_option_value;
142 ELSE
143 l_status := FALSE;
144 END IF;
145 ELSE
146 l_status := FALSE;
147 END IF;
148
149
150 -- End reading source options
151 IF l_status
152 THEN
153 edw_log.put_line (
154 'Object Settings: Degree of Parallelism : '
155 || g_parallel,FND_LOG.LEVEL_STATEMENT
156 );
157 edw_log.put_line (
158 'Object Settings: Commit Size : '
159 || g_push_size,FND_LOG.LEVEL_STATEMENT
160 );
161 edw_log.put_line (
162 'Object Settings: Operational Tablespace : '
163 || g_op_tablespace,FND_LOG.LEVEL_STATEMENT
164 );
165 edw_log.put_line (
166 'Object Settings: Rollback Segment : '
167 || g_rbs,FND_LOG.LEVEL_STATEMENT
168 );
169
170 /*l_dir := fnd_profile.VALUE ('EDW_LOGFILE_DIR');
171
172 IF l_dir IS NULL
173 THEN
174 l_dir := '/sqlcom/log';
175 END IF;
176 */
177 l_dir := fnd_profile.value('UTL_FILE_LOG');
178 if l_dir is null then
179 l_dir := fnd_profile.value('EDW_LOGFILE_DIR');
180 if l_dir is null then
181 l_dir:='/sqlcom/log';
182 end if;
183 end if;
184
185 g_transport_data := p_transport_data;
186 g_start_time := SYSDATE;
187
188 IF g_transport_data
189 THEN
190 put_debug_msg ('Transportation mode is set to TRUE');
191 ELSE
192 put_debug_msg ('Transportation mode is set to FALSE');
193 END IF;
194
195 edw_log.put_names (
196 p_object_name
197 || '.log',
198 p_object_name
199 || '.out',
200 l_dir
201 );
202 edw_log.put_line (
203 'Starting the collection program for object '
204 || p_object_name,FND_LOG.LEVEL_PROCEDURE
205 );
206 edw_log.put_line (' ',FND_LOG.LEVEL_PROCEDURE);
207 edw_log.put_line (
208 'System time at the start of the process is :'
209 || fnd_date.date_to_displaydt (g_start_time),FND_LOG.LEVEL_PROCEDURE
210 );
211 edw_log.put_line (' ',FND_LOG.LEVEL_PROCEDURE);
212
213
214 -- Get DB Link Names
215
216 BEGIN
217 get_dblink_names (g_source_link, g_target_link);
218 EXCEPTION
219 WHEN OTHERS
220 THEN
221 RETURN (FALSE);
222 END;
223
224 -- Determine object type
225 edw_log.put_line('Calling get_object_type to determine if dimension or fact',FND_LOG.LEVEL_PROCEDURE);
226 if get_object_type(p_object_name) = true then
227 edw_log.put_line('The object name and type are '||p_object_name||', '||g_object_type,FND_LOG.LEVEL_PROCEDURE);
228 end if;
229 edw_log.put_line( ' ',FND_LOG.LEVEL_PROCEDURE);
230
231 -- ---------------------------------------------------------------------------
232 -- Cache global values
233 -- ---------------------------------------------------------------------------
234
235 g_object_name := p_object_name;
236
237 SELECT edw_mapping_seq.NEXTVAL
238 INTO g_request_id
239 FROM DUAL;
240
241 g_source_same_as_target := source_same_as_target;
242
243
244 -- CLEAN UP Temporary Tables
245 -- Get List of Staging tables for EDW object
246
247 IF g_staging_table IS NULL
248 THEN
249 get_stg_table_names (g_object_name, tab_stglist);
250 ELSE
251
252 -- not registrated owb object
253 g_object_type := edw_fact;
254 tab_stglist (0).tbl_name := g_staging_table;
255 tab_stglist (0).tbl_owner := get_syn_info (g_staging_table);
256 g_staging_table := NULL;
257 END IF;
258
259 IF g_source_same_as_target = FALSE
260 THEN
261 clean_up (tab_stglist, '_SK');
262 clean_up (tab_stglist, '_SL');
263
264 -- clean up local staging tables
265 IF is_object_for_local_load (UPPER (g_object_name)) = FALSE
266 THEN
267 put_debug_msg ('Truncating the interface tables');
268 truncate_stg (tab_stglist);
269 ELSE
270 put_debug_msg (
271 'NOT Truncating the interface tables because this object is for local load'
272 );
273 END IF;
274 END IF; -- source_same_as_target
275
276 edw_collection_util.get_push_globals (
277 p_staging_table_name=> p_object_name
278 );
279
280 -- call auto dangling recovery for dimensions
281 -- check object type
282 -- check if single instance is implemented
283
284 IF g_object_type = edw_dim
285 THEN
286 put_debug_msg ('Running Auto Dangling Recovery Function');
287
288 IF g_source_same_as_target
289 THEN
290 l_status :=
291 edw_src_dang_recovery.get_dangling_keys (
292 p_object_name,
293 NULL,
294 p_pk_view,
295 p_missing_key_view
296 );
297 ELSE
298 l_status :=
299 edw_src_dang_recovery.get_dangling_keys (
300 p_object_name,
301 g_target_link,
302 p_pk_view,
303 p_missing_key_view
304 );
305 END IF;
306
307 IF l_status = FALSE
308 THEN
309 edw_log.put_line (' ',FND_LOG.LEVEL_ERROR);
310 edw_log.put_line ('Auto Dangling Recovery Error.',FND_LOG.LEVEL_ERROR);
311 edw_log.put_line (edw_src_dang_recovery.g_status_message,FND_LOG.LEVEL_ERROR);
312 END IF;
313 END IF; -- end if for checking EDW Object Type
314 END IF; -- l_status = TRUE (options read completed).
315 ELSE
316 edw_log.put_line ('Error: Installation of BIS Product not found.',FND_LOG.LEVEL_ERROR);
317 edw_log.put_line (' ',FND_LOG.LEVEL_ERROR);
318 l_status := FALSE;
319 END IF;
320
321 put_debug_msg ('********** End Setup Function');
322 RETURN l_status;
323 EXCEPTION
324 WHEN OTHERS
325 THEN
326 edw_log.put_line (
327 ' Setup of '
328 || p_object_name
329 || ' tables failed. Error.',FND_LOG.LEVEL_ERROR
330 );
331 RETURN (FALSE);
332 END setup;
333
334
335 /*---------------------------------------------------------------------------
336 Function setup is overloaded for backward compatibility.
337 If the new de-coupled architecture is used, local and remote
338 staging table name should be passed to setup
339 -----------------------------------------------------------------------------*/
340 FUNCTION setup (
341 p_object_name IN VARCHAR2,
342 p_local_staging_table IN VARCHAR2,
343 p_remote_staging_table IN VARCHAR2,
344 p_exception_msg OUT NOCOPY VARCHAR2
345 )
346 RETURN BOOLEAN
347 IS
348 Begin
349 if setup(p_object_name,p_local_staging_table,p_remote_staging_table,p_exception_msg,
350 null,null,TRUE)=false then
351 return false;
352 end if;
353 return true;
354 EXCEPTION
355 WHEN OTHERS
356 THEN
357 g_errbuf := SQLERRM;
358 g_retcode := SQLCODE;
359 p_exception_msg := g_errbuf
360 || ':'
361 || g_retcode;
362 RETURN FALSE;
363 END setup;
364 FUNCTION setup (
365 p_object_name IN VARCHAR2,
366 p_local_staging_table IN VARCHAR2,
367 p_remote_staging_table IN VARCHAR2,
368 p_exception_msg OUT NOCOPY VARCHAR2,
369 p_pk_view IN VARCHAR2,
370 p_missing_key_view IN VARCHAR2,
371 p_transport_data IN BOOLEAN
372 )
373 RETURN BOOLEAN
374 IS
375 BEGIN
379 'Local Staging Table Name is Different From Remote Staging Table Name. Error.',FND_LOG.LEVEL_STATEMENT
376 IF p_local_staging_table <> p_remote_staging_table
377 THEN
378 edw_log.put_line (
380 );
381 edw_log.put_line (' ',FND_LOG.LEVEL_STATEMENT);
382 RAISE g_push_remote_failure;
383 END IF;
384
385 g_staging_table := p_remote_staging_table;
386 RETURN setup (
387 p_object_name=> p_object_name,
388 p_pk_view=> p_pk_view,
389 p_missing_key_view=> p_missing_key_view,
390 p_transport_data=> p_transport_data
391 );
392 EXCEPTION
393 WHEN OTHERS
394 THEN
395 g_errbuf := SQLERRM;
396 g_retcode := SQLCODE;
397 p_exception_msg := g_errbuf
398 || ':'
399 || g_retcode;
400 RETURN FALSE;
401 END setup;
402
403
404 /*---------------------------------------------------------------------------
405 Function to get the object type
406 -----------------------------------------------------------------------------*/
407 FUNCTION get_object_type (p_object IN VARCHAR2)
408 RETURN BOOLEAN
409 IS
410 l_stmt VARCHAR2 (5000);
411 l_object_type VARCHAR2 (30);
412 cv curtyp;
413 l_status BOOLEAN;
414 BEGIN
415 put_debug_msg (
416 '********** Running EDW_COLLECTION_UTIL.get_object_type'
417 );
418 put_debug_msg ( 'Checking EDW object '
419 || p_object
420 || ' type.');
421 l_stmt := 'select ''DIMENSION'' from EDW_DIMENSIONS_MD_V@'
422 || g_target_link
423 || ' where dim_name =:d '
424 || ' union '
425 || ' select ''FACT'' from EDW_FACTS_MD_V@'
426 || g_target_link
427 || ' where fact_name=:f ';
428 OPEN cv FOR l_stmt USING p_object, p_object;
429 FETCH cv INTO l_object_type;
430 CLOSE cv;
431
432 IF l_object_type = edw_dim
433 OR l_object_type = edw_fact
434 THEN
435 g_object_type := l_object_type;
436 l_status := TRUE;
437 ELSE
438 l_status := FALSE;
439 END IF;
440
441 put_debug_msg ( 'EDW Object type is: '
442 || l_object_type);
443 put_debug_msg ('********** End EDW_COLLECTION_UTIL.get_object_type');
444 RETURN l_status;
445 EXCEPTION
446 WHEN NO_DATA_FOUND
447 THEN
448 BEGIN
449 CLOSE cv;
450 EXCEPTION
451 WHEN OTHERS
452 THEN
453 NULL;
454 END;
455
456 g_object_type := edw_fact;
457 RETURN TRUE;
458 WHEN OTHERS
459 THEN
460 BEGIN
461 CLOSE cv;
462 EXCEPTION
463 WHEN OTHERS
464 THEN
465 NULL;
466 END;
467
468 RETURN FALSE;
469 END get_object_type;
470
471
472 -- ------------------------------------------------------------------
473 -- Name: Wrapup
474 -- Desc: Initial function called to setup log file and push program
475 -- ------------------------------------------------------------------
476 PROCEDURE wrapup (
477 p_sucessful IN BOOLEAN
478 ) is
479 Begin
480 wrapup (p_sucessful,0,null,null,null);
481 EXCEPTION
482 WHEN OTHERS THEN
483 edw_log.put_line ( 'Wrapup: '|| SQLERRM,FND_LOG.LEVEL_ERROR);
484 RAISE;
485 END wrapup;
486 PROCEDURE wrapup(
487 p_sucessful IN BOOLEAN,
488 p_rows_inserted IN NUMBER
489 ) is
490 Begin
491 wrapup (p_sucessful,p_rows_inserted,null,null,null);
492 EXCEPTION
493 WHEN OTHERS THEN
494 edw_log.put_line ( 'Wrapup: '|| SQLERRM,FND_LOG.LEVEL_ERROR);
495 RAISE;
496 END wrapup;
497 /*
498 Bug 2875426
499 This API is only meant for EDW_UNSPSC_M_C
500 This API DOES NOT populate the from and to dates. No collection program must
501 call it!
502 */
503 PROCEDURE wrapup(
504 p_sucessful IN BOOLEAN,
505 p_rows_inserted IN NUMBER,
506 p_exception_msg IN VARCHAR2
507 ) is
508 Begin
509 wrapup (p_sucessful,p_rows_inserted,p_exception_msg,null,null);
510 EXCEPTION
511 WHEN OTHERS THEN
512 edw_log.put_line ( 'Wrapup: '|| SQLERRM,FND_LOG.LEVEL_ERROR);
513 RAISE;
514 END wrapup;
515 PROCEDURE wrapup(
516 p_sucessful IN BOOLEAN,
517 p_rows_inserted IN NUMBER,
518 p_period_start IN DATE,
519 p_period_end IN DATE
520 ) is
521 Begin
522 wrapup (p_sucessful,p_rows_inserted,null,p_period_start,p_period_end);
523 EXCEPTION
524 WHEN OTHERS THEN
525 edw_log.put_line ( 'Wrapup: '|| SQLERRM,FND_LOG.LEVEL_ERROR);
526 RAISE;
527 END wrapup;
528 PROCEDURE wrapup (
532 p_period_start IN DATE,
529 p_sucessful IN BOOLEAN,
530 p_rows_inserted IN NUMBER,
531 p_exception_msg IN VARCHAR2,
533 p_period_end IN DATE
534 )
535 IS
536 l_rows_inserted INTEGER := 0;
537 l_sucessful BOOLEAN := TRUE;
538 BEGIN
539 put_debug_msg ('********** Starting EDW_COLLECTION_UTIL.wrapup');
540 if p_sucessful then
541 edw_log.put_line ('Wrapup called with SUCCESS',FND_LOG.LEVEL_STATEMENT);
542 else
543 edw_log.put_line ('Wrapup called with ERROR and message is '||p_exception_msg,FND_LOG.LEVEL_ERROR);
544 end if;
545 IF (p_sucessful)
546 THEN
547 /*
548 Possible cases:
549 1. Direct insert into remote staging tables
550 (row count in local staging tables equal zero)
551 2. Data transportation from local staging tables to remote
552 staging tables using EDW Generic transportation model
553
554 Running PL/SQL table based push_to_target
555 */
556 -- update function input parameter
557
558 BEGIN
559 l_rows_inserted := push_to_target;
560 edw_log.put_line (
561 'Rows Inserted into Interface Tables: '
562 || p_rows_inserted,FND_LOG.LEVEL_STATEMENT
563 );
564 edw_log.put_line (' ',FND_LOG.LEVEL_STATEMENT);
565 IF l_rows_inserted = -1
566 THEN
567 l_sucessful := FALSE;
568 ELSIF ( l_rows_inserted = 0 ) AND
569 (g_source_same_as_target = FALSE)
570 AND (p_rows_inserted > 0)
571 THEN
572 l_rows_inserted := p_rows_inserted;
573 l_sucessful := TRUE;
574 ELSIF
575 ( l_rows_inserted = 0 ) AND
576 (g_source_same_as_target = TRUE)
577 THEN
578 l_rows_inserted := p_rows_inserted;
579 l_sucessful := TRUE;
580 END IF;
581
582 EXCEPTION
583 WHEN OTHERS
584 THEN
585 edw_log.put_line (' Push Data Failed. Error.',FND_LOG.LEVEL_ERROR);
586 l_sucessful := FALSE;
587 END;
588 END IF; -- end if for p_sucessful
589
590 IF (g_source_same_as_target = FALSE)
591 AND (is_object_for_local_load (UPPER (g_object_name)) = FALSE)
592 THEN
593 IF NOT g_debug
594 THEN
595 -- clean up local staging tables in case of decoupled architecture
596 truncate_stg (tab_stglist);
597 ELSE
598 IF tab_stglist.COUNT > 0
599 THEN
600 put_debug_msg ('Supposed to truncate the interface tables');
601
602 FOR i IN tab_stglist.FIRST .. tab_stglist.LAST
603 LOOP
604 edw_log.put_line (tab_stglist (i).tbl_name,FND_LOG.LEVEL_STATEMENT);
605 END LOOP;
606 END IF;
607 END IF; -- g_debug
608 ELSE
609 put_debug_msg (
610 'NOT Truncating the interface tables for this object as its for local load'
611 );
612 END IF;
613
614 -- clean up temporary tables in case if debug is switched off
615 IF (g_source_same_as_target = FALSE) AND (g_debug = FALSE)
616 THEN
617 clean_up (tab_stglist, '_SK');
618 clean_up (tab_stglist, '_SL');
619 END IF;
620
621 IF (l_sucessful AND p_sucessful)
622 THEN
623
624 -- ---------------------------------------------------------------------------
625 -- Print ending messages
626 -- ---------------------------------------------------------------------------
627 edw_log.put_line ('System time at the end of data push is :',FND_LOG.LEVEL_PROCEDURE);
628 put_timestamp;
629 edw_log.put_line ('---------------------------------------------',FND_LOG.LEVEL_PROCEDURE);
630
631 -- ---------------------------------------------------------------------------
632 -- Get the current time from the Warehouse
633 -- ---------------------------------------------------------------------------
634 edw_log.put_line (' ',FND_LOG.LEVEL_PROCEDURE);
635 edw_log.put_line (
636 'Getting the current push date in warehouse time from warehouse',FND_LOG.LEVEL_PROCEDURE
637 );
638 edw_log.put_line (' ',FND_LOG.LEVEL_PROCEDURE);
639 edw_collection_util.set_push_end_dates;
640
641 -- ---------------------------------------------------------------------------
642 -- Insert a row in collection log table for this collection run
643 -- ---------------------------------------------------------------------------
644
645
646 edw_collection_util.staging_log (
647 p_exception_message=> NULL,
648 p_status=> 'SUCCESS',
649 p_no_of_records=> l_rows_inserted,
650 p_period_start=> p_period_start,
651 p_period_end=> p_period_end
652 );
653 COMMIT;
654 ELSE
655
656 -- ---------------------------------------------------------------------------
657 -- Print ending messages
661 edw_log.put_line ('---------------------------------------------',FND_LOG.LEVEL_ERROR);
658 -- ---------------------------------------------------------------------------
659 edw_log.put_line ('System time at the time of exception is :',FND_LOG.LEVEL_ERROR);
660 put_timestamp;
662
663 -- ---------------------------------------------------------------------------
664 -- Insert a row in collection log table for this collection run
665 -- with a status of ERROR
666 -- ---------------------------------------------------------------------------
667 edw_log.put_line ('Exception Handling: Following error encountered',FND_LOG.LEVEL_ERROR);
668 edw_log.put_line (p_exception_msg,FND_LOG.LEVEL_ERROR);
669 edw_collection_util.staging_log (
670 p_exception_message=> p_exception_msg,
671 p_status=> 'ERROR',
672 p_no_of_records=> 0,
673 p_period_start=> p_period_start,
674 p_period_end=> p_period_end
675 );
676 edw_log.put_line (
677 'Inserted error message into the edw_push_detail_log',FND_LOG.LEVEL_ERROR
678 );
679 COMMIT;
680
681
682 --replacing l_sucessful1 with l_sucessful (bug 2350968)
683 IF (NOT l_sucessful)
684 THEN
685 RAISE g_push_remote_failure;
686 END IF;
687 END IF;
688
689 edw_log.put_line (' ',FND_LOG.LEVEL_PROCEDURE);
690 edw_log.put_line ('System time at the end of the process is :',FND_LOG.LEVEL_PROCEDURE);
691 put_timestamp;
692 edw_log.put_line (' ',FND_LOG.LEVEL_PROCEDURE);
693 edw_log.put_line (
694 'Total elapsed time: '
695 || edw_log.duration ( SYSDATE
696 - g_start_time),FND_LOG.LEVEL_PROCEDURE
697 );
698
699 IF (fnd_profile.VALUE ('EDW_TRACE') = 'Y')
700 THEN
701 null;
702 END IF;
703
704 put_debug_msg ('********** End EDW_COLLECTION_UTIL.wrapup');
705 EXCEPTION
706 WHEN g_push_remote_failure
707 THEN
708 edw_log.put_line ( 'error '
709 || g_retcode
710 || ':'
711 || g_errbuf,FND_LOG.LEVEL_ERROR);
712 edw_log.put_line (
713 'Data migration from local to remote staging have failed',FND_LOG.LEVEL_ERROR
714 );
715 RAISE;
716 WHEN OTHERS
717 THEN
718 edw_log.put_line ( 'Wrapup: '
719 || SQLERRM,FND_LOG.LEVEL_ERROR);
720 RAISE;
721 END wrapup;
722
723 FUNCTION get_wh_language
724 RETURN VARCHAR2
725 IS
726 l_lang VARCHAR2 (240);
727 BEGIN
728 put_debug_msg (
729 '********** Starting EDW_COLLECTION_UTIL.get_wh_language'
730 );
731
732 SELECT edw_language_code
733 INTO l_lang
734 FROM edw_local_system_parameters;
735
736 put_debug_msg ('********** End EDW_COLLECTION_UTIL.get_wh_language');
737 RETURN l_lang;
738 EXCEPTION
739 WHEN OTHERS
740 THEN
741 RETURN 'US';
742 END get_wh_language;
743
744
745 /*
746 FUNCTION get_level_dp(p_lookup_code in varchar2) return VARCHAR2 IS
747 l_meaning VARCHAR2(100);
748 l_lang VARCHAR2(240);
749 BEGIN
750
751 l_lang := get_wh_language;
752
753 SELECT
754 meaning
755 INTO
756 l_meaning
757 FROM fnd_lookup_values_vl@edw_apps_to_wh
758 WHERE lookup_code= p_lookup_code
759 AND lookup_type= 'EDW_LEVEL_LOOKUP';
760
761 return l_meaning;
762
763 EXCEPTION WHEN OTHERS THEN
764 return 'NOT FOUND';
765 END;
766 */
767
768
769 FUNCTION get_lookup_value (
770 p_lookup_type IN VARCHAR2,
771 p_lookup_code IN VARCHAR2
772 )
773 RETURN VARCHAR2
774 IS
775 l_meaning VARCHAR2 (100);
776 l_lang VARCHAR2 (240);
777 l_stmt VARCHAR2 (1000);
778 cv curtyp;
779 BEGIN
780 put_debug_msg ('************* Running get_lookup_value function');
781
782 IF (g_source_link IS NULL)
783 OR (g_target_link IS NULL)
784 THEN
785 get_dblink_names (g_source_link, g_target_link);
786 END IF;
787
788 l_lang := get_wh_language;
789 put_debug_msg ('Executing:');
790 l_stmt :=
791 'SELECT
792 meaning
793 FROM fnd_lookup_values_vl@'
794 || g_target_link
795 || '
796 WHERE upper(lookup_type)= upper(:s1)
797 AND upper(lookup_code)= upper(:s2) ';
798 put_debug_msg (l_stmt);
799 OPEN cv FOR l_stmt USING p_lookup_type, p_lookup_code;
800 FETCH cv INTO l_meaning;
801 CLOSE cv;
802 put_debug_msg ('************* End of get_lookup_value');
803 RETURN l_meaning;
804 EXCEPTION
805 WHEN OTHERS
806 THEN
807 RETURN NULL;
808 END get_lookup_value;
809
810 FUNCTION get_wh_lookup_value (
811 p_lookup_type IN VARCHAR2,
815 IS
812 p_lookup_code IN VARCHAR2
813 )
814 RETURN VARCHAR2
816 l_meaning VARCHAR2 (100);
817 l_lang VARCHAR2 (240);
818 l_stmt VARCHAR2 (1000);
819 cv curtyp;
820 BEGIN
821 put_debug_msg ('************* Running get_wh_lookup_value');
822 l_lang := get_wh_language;
823 put_debug_msg ('Executing: ');
824 l_stmt :=
825 'SELECT
826 meaning
827 FROM fnd_lookup_values_vl@'
828 || g_target_link
829 || '
830 WHERE upper(lookup_type)= upper(:s1)
831 AND upper(lookup_code)= upper(:s2) ';
832 put_debug_msg (l_stmt);
833 OPEN cv FOR l_stmt USING p_lookup_type, p_lookup_code;
834 FETCH cv INTO l_meaning;
835 CLOSE cv;
836 RETURN l_meaning;
837 EXCEPTION
838 WHEN OTHERS
839 THEN
840 RETURN 'EDW_NOT_FOUND';
841 END get_wh_lookup_value;
842
843 PROCEDURE sendnote (
844 name_of_conc_program IN VARCHAR2,
845 TYPE IN VARCHAR2,
846 status IN VARCHAR2,
847 message IN VARCHAR2
848 )
849 IS
850 l_date VARCHAR2 (30);
851 l_username VARCHAR2 (30);
852 c_item_type VARCHAR2 (30);
853 c_item_key VARCHAR2 (50);
854 c_process VARCHAR2 (30);
855 BEGIN
856 put_debug_msg ('************* Running sendnote procedure');
857 l_date := fnd_date.date_to_displaydt (SYSDATE);
858 l_username := NVL (fnd_profile.VALUE ('EDW_WF_ROLE'), 'MFG');
859 c_item_type := 'EDW_NOTE';
860 c_item_key := NVL (name_of_conc_program, '1')
861 || NVL (TYPE, '1')
862 || NVL (status, '1')
863 || l_username;
864 c_process := 'EDW_SENDPROC';
865 wf_purge.total (c_item_type, c_item_key, SYSDATE);
866 wf_engine.createprocess (
867 itemtype=> c_item_type,
868 itemkey=> c_item_key,
869 process=> c_process
870 );
871 wf_engine.setitemattrtext (
872 itemtype=> c_item_type,
873 itemkey=> c_item_key,
874 aname => 'DATE',
875 avalue=> l_date
876 );
877 wf_engine.setitemattrtext (
878 itemtype=> c_item_type,
879 itemkey=> c_item_key,
880 aname => 'USERNAME',
881 avalue=> l_username
882 );
883 wf_engine.setitemattrtext (
884 itemtype=> c_item_type,
885 itemkey=> c_item_key,
886 aname => 'NAME_OF_CONC_PROGRAM',
887 avalue=> name_of_conc_program
888 );
889 wf_engine.setitemattrtext (
890 itemtype=> c_item_type,
891 itemkey=> c_item_key,
892 aname => 'TYPE',
893 avalue=> TYPE
894 );
895 wf_engine.setitemattrtext (
896 itemtype=> c_item_type,
897 itemkey=> c_item_key,
898 aname => 'STATUS',
899 avalue=> status
900 );
901 wf_engine.setitemattrtext (
902 itemtype=> c_item_type,
903 itemkey=> c_item_key,
904 aname => 'MESSAGE',
905 avalue=> message
906 );
907 wf_engine.startprocess (itemtype => c_item_type, itemkey => c_item_key);
908 put_debug_msg ('************* End of sendnote procedure');
909 END sendnote;
910
911
912 -- ------------------------------------------------------------------
913 -- Name: Get_Push_Globals
914 -- Desc:
915 -- ------------------------------------------------------------------
916 PROCEDURE get_push_globals (p_staging_table_name IN VARCHAR2)
917 IS
918 l_stmt VARCHAR2 (2000);
919 l_cursor INTEGER;
920 l_dummy INTEGER;
921 BEGIN
922 put_debug_msg (
923 '********** Running Procedure : Get_push_globals for EDW Object '
924 || p_staging_table_name
925 );
926 -- Clear all the globals
927 g_instance_code := NULL;
928 g_user_id := 0;
929 g_login_id := 0;
930 g_default_rate_type := NULL;
931 g_global_currency := NULL;
932 g_wh_curr_push_start_date := NULL;
933 g_wh_curr_push_end_date := NULL;
934 g_local_curr_push_start_date := NULL;
935 g_local_curr_push_end_date := NULL;
936 g_local_last_push_start_date := NULL;
937
938
939 -- ----------------------------------------------------------------------------
940 -- Get Instance Code
941 -- ----------------------------------------------------------------------------
942 BEGIN
943 SELECT instance_code
944 INTO g_instance_code
945 FROM edw_local_instance;
946 EXCEPTION
947 WHEN OTHERS
948 THEN
949 edw_log.put_line (' Instance code not found. Error.',FND_LOG.LEVEL_ERROR);
950 END;
951
952
953 -- ----------------------------------------------------------------------------
954 -- Get the current time in local clock
955 -- ----------------------------------------------------------------------------
956 g_local_curr_push_start_date := SYSDATE;
957
961
958 -- -----------------------------------------------------------------------------
959 -- Get the current time from the Warehouse
960 -- ----------------------------------------------------------------------------
962 l_cursor := DBMS_SQL.open_cursor;
963 /* see if the end period for the previous push exists */
964
965
966
967
968
969 l_stmt := 'select sysdate
970 from dual@'
971 || g_target_link;
972 DBMS_SQL.parse (l_cursor, l_stmt, DBMS_SQL.native);
973 DBMS_SQL.define_column (l_cursor, 1, g_wh_curr_push_start_date);
974 l_dummy := DBMS_SQL.EXECUTE (l_cursor);
975
976 IF DBMS_SQL.fetch_rows (l_cursor) <> 0
977 THEN
978 DBMS_SQL.column_value (l_cursor, 1, g_wh_curr_push_start_date);
979 END IF;
980
981 DBMS_SQL.close_cursor (l_cursor);
982
983
984 -- ----------------------------------------------------------------------------
985 -- get the start date of the last push
986 -- ----------------------------------------------------------------------------
987 -- -------------------------------------------------------------------------
988 -- If this is the first time that the global values are to be retrieved, then
989 -- there will be no entries in the log table. In that case, we should
990 -- get the right date from edw_staging_table as the default.
991 -- currently assigning 01-jan-1950 date as the default. <DEBUG: Need change>
992 -- ------------------------------------------------------------------------
993
994 BEGIN
995
996 -- for bug 2135826
997 g_local_last_push_start_date :=
998 fnd_date.displaydt_to_date (
999 get_last_push_date (p_staging_table_name)
1000 );
1001
1002
1003 ------
1004
1005
1006 /* the following logic should be obsoleted once all product teams
1007 start passing the range parameters to the wrapup routine */
1008
1009 IF g_local_last_push_start_date IS NULL
1010 THEN
1011 put_debug_msg (
1012 ' There are no entries in the log table for last push start and end date'
1013 );
1014 put_debug_msg (' Assigning default start date as 01/01/1950');
1015 g_local_last_push_start_date :=
1016 TO_DATE ('01/01/1950', 'MM/DD/YYYY');
1017 END IF;
1018 END;
1019
1020
1021 -- ----------------------------------------------------------------------------
1022 -- Get the offset from profile
1023 -- ----------------------------------------------------------------------------
1024 --g_offset := nvl(fnd_profile.value('EDW_COLLECTION_OFFSET') / 24, 0);
1025 g_offset := 0;
1026
1027 -- ----------------------------------------------------------------------------
1028 -- Assign user_id and login_id to the local variables
1029 -- ----------------------------------------------------------------------------
1030
1031 g_user_id := fnd_global.user_id;
1032 g_login_id := fnd_global.login_id;
1033 put_debug_msg (
1034 ' Retrieved the instance code :'
1035 || g_instance_code
1036 );
1037 put_debug_msg (' Start push time in wh clock IS :');
1038 put_debug_msg (
1039 fnd_date.date_to_displaydt (g_local_curr_push_start_date)
1040 );
1041 put_debug_msg (' Last local_push_start_date IS : ');
1042 put_debug_msg (
1043 fnd_date.date_to_displaydt (g_local_last_push_start_date)
1044 );
1045 put_debug_msg ( 'User ID is :'
1046 || g_user_id);
1047 put_debug_msg ( 'Login ID is :'
1048 || g_login_id);
1049 put_debug_msg ('********** End Procedure : Get_Push_Globals');
1050 END get_push_globals;
1051
1052
1053 -- ------------------------------------------------------------------
1054 -- Name: Staging_Log
1055 -- Desc: Logs each push into EDW_PUSH_DETAIL_LOG
1056 -- Striped by Object Name and Instance
1057 -- A new row is added in the EDW_PUSH_LOG only when the
1058 -- collection_request_id is not null, otherwise only an update .
1059 -- ------------------------------------------------------------------
1060 PROCEDURE staging_log (
1061 p_no_of_records IN NUMBER,
1062 p_status IN VARCHAR2,
1063 p_exception_message IN VARCHAR2
1064 ) is
1065 Begin
1066 staging_log (p_no_of_records,p_status,p_exception_message,null,null);
1067 EXCEPTION
1068 WHEN OTHERS
1069 THEN
1070 edw_log.put_line (
1071 'Error inserting into local log table '
1072 || SQLERRM,FND_LOG.LEVEL_ERROR
1073 );
1074 RAISE;
1075 END staging_log;
1076 PROCEDURE staging_log (
1077 p_no_of_records IN NUMBER,
1078 p_status IN VARCHAR2,
1079 p_exception_message IN VARCHAR2,
1080 p_period_start IN DATE,
1081 p_period_end IN DATE
1082 )
1083 IS
1084 l_conc_id INTEGER;
1085 l_elementid NUMBER (9);
1086 l_dummy INTEGER;
1087 cid INTEGER;
1088 l_stmt VARCHAR2 (1000);
1092 put_debug_msg (
1089 cv curtyp;
1090 BEGIN
1091 put_debug_msg ('********** Run Procedure : Staging_Log');
1093 'Insert into edw_push_detail_log a row for this collection run'
1094 );
1095 put_debug_msg ( 'p_period_start is '
1096 || p_period_start);
1097 put_debug_msg ( 'p_period_end is '
1098 || p_period_end);
1099 l_stmt := 'select relation_id from edw_relations_md_v@'
1100 || g_target_link
1101 || ' where relation_name=:s';
1102 put_debug_msg ('Running: ');
1103 put_debug_msg (l_stmt);
1104 OPEN cv FOR l_stmt USING g_object_name;
1105 FETCH cv INTO l_elementid;
1106 CLOSE cv;
1107 l_conc_id := fnd_global.conc_request_id;
1108 cid := DBMS_SQL.open_cursor;
1109 /* Insert into the Detail */
1110 l_stmt :=
1111 'INSERT INTO EDW_Push_Detail_Log@'
1112 || g_target_link
1113 || '(
1114 INSTANCE_CODE,
1115 PUSH_STATUS,
1116 PUSH_START_DATE,
1117 PUSH_END_DATE,
1118 WH_PUSH_START_DATE,
1119 WH_PUSH_END_DATE,
1120 NO_OF_PUSHED_RECORDS,
1121 PUSH_EXCEPTION_MESSAGE,
1122 CREATED_BY,
1123 CREATION_DATE,
1124 LAST_UPDATE_DATE,
1125 LAST_UPDATE_BY,
1126 LAST_UPDATE_LOGIN,
1127 PERIOD_START,
1128 PERIOD_END, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE,
1129 PUSH_CONCURRENT_ID)
1130 VALUES( :x_instance, :x_status, :x_start, :x_end, :x_whstart, :x_whend,
1131 :x_no_pushed, :x_message, :x_createdby, :x_creationdate, :x_lastupddate,
1132 :x_lastupdby, :x_lastupdlogin, :x_period_start, :x_period_end,
1133 :x_objname, :x_objid, :x_objtype, :x_concid)';
1134 put_debug_msg ('Running: ');
1135 put_debug_msg (l_stmt);
1136 DBMS_SQL.parse (cid, l_stmt, DBMS_SQL.native);
1137 DBMS_SQL.bind_variable (cid, ':x_instance', g_instance_code);
1138 DBMS_SQL.bind_variable (cid, ':x_status', p_status);
1139 DBMS_SQL.bind_variable (cid, ':x_start', g_local_curr_push_start_date);
1140 DBMS_SQL.bind_variable (cid, ':x_end', g_local_curr_push_end_date);
1141 DBMS_SQL.bind_variable (cid, ':x_whstart', g_wh_curr_push_start_date);
1142 DBMS_SQL.bind_variable (cid, ':x_whend', g_wh_curr_push_end_date);
1143 DBMS_SQL.bind_variable (cid, ':x_no_pushed', p_no_of_records);
1144 DBMS_SQL.bind_variable (cid, ':x_message', p_exception_message);
1145 DBMS_SQL.bind_variable (cid, ':x_createdby', g_user_id);
1146 DBMS_SQL.bind_variable (
1147 cid,
1148 ':x_creationdate',
1149 g_wh_curr_push_start_date
1150 );
1151 DBMS_SQL.bind_variable (
1152 cid,
1153 ':x_lastupddate',
1154 g_wh_curr_push_start_date
1155 );
1156 DBMS_SQL.bind_variable (cid, ':x_lastupdby', g_user_id);
1157 DBMS_SQL.bind_variable (cid, ':x_lastupdlogin', g_login_id);
1158 DBMS_SQL.bind_variable (cid, ':x_period_start', p_period_start);
1159 DBMS_SQL.bind_variable (cid, ':x_period_end', p_period_end);
1160 DBMS_SQL.bind_variable (cid, ':x_objname', g_object_name);
1161 DBMS_SQL.bind_variable (cid, ':x_objid', l_elementid);
1162 DBMS_SQL.bind_variable (cid, ':x_objtype', g_object_type);
1163 DBMS_SQL.bind_variable (cid, ':x_concid', l_conc_id);
1164 l_dummy := DBMS_SQL.EXECUTE (cid);
1165 DBMS_SQL.close_cursor (cid);
1166
1167 put_debug_msg ('********** End Procedure : Staging_Log');
1168 EXCEPTION
1169 WHEN OTHERS
1170 THEN
1171 edw_log.put_line (
1172 'Error inserting into local log table '
1173 || SQLERRM,FND_LOG.LEVEL_ERROR
1174 );
1175 RAISE;
1176 END staging_log;
1177
1178
1179 -- ------------------------------------------------------------------
1180 -- Name: Set_Push_End_dates
1181 -- Desc:
1182 -- ------------------------------------------------------------------
1183 PROCEDURE set_push_end_dates
1184 IS
1185 l_stmt VARCHAR2 (1000);
1186 l_cursor INTEGER;
1187 l_dummy INTEGER;
1188 BEGIN
1189 put_debug_msg ('********** Start Procedure : Set_Push_End_dates');
1190 put_debug_msg (
1191 ' Get current time in wh clock in '
1192 || g_wh_curr_push_end_date
1193 );
1194 l_cursor := DBMS_SQL.open_cursor;
1195 /* see if the end period for the previous push exists */
1196
1197
1198 l_stmt := 'select sysdate
1199 from dual@'
1200 || g_target_link;
1201 DBMS_SQL.parse (l_cursor, l_stmt, DBMS_SQL.native);
1202 DBMS_SQL.define_column (l_cursor, 1, g_wh_curr_push_end_date);
1203 l_dummy := DBMS_SQL.EXECUTE (l_cursor);
1204
1205 IF DBMS_SQL.fetch_rows (l_cursor) <> 0
1206 THEN
1207 DBMS_SQL.column_value (l_cursor, 1, g_wh_curr_push_end_date);
1208 END IF;
1209
1210 DBMS_SQL.close_cursor (l_cursor);
1211 g_local_curr_push_end_date := SYSDATE;
1212 put_debug_msg (' G_wh_curr_push_end_date IS :');
1213 put_debug_msg (fnd_date.date_to_displaydt (g_wh_curr_push_end_date));
1217
1214 put_debug_msg ('********** End Procedure : Set_Push_End_Dates');
1215 END set_push_end_dates;
1216
1218 -- ------------------------------------------------------------------
1219 -- Name: set_wh_language
1220 -- Desc:
1221 -- ------------------------------------------------------------------
1222 FUNCTION set_wh_language
1223 RETURN BOOLEAN
1224 IS
1225 l_lang_code VARCHAR2 (240);
1226 l_nls_language VARCHAR2 (240);
1227 cid INTEGER;
1228 l_dummy_int INTEGER;
1229 BEGIN
1230 put_debug_msg ('********** Start Procedure : set_wh_language');
1231
1232 SELECT edw_language_code
1233 INTO l_lang_code
1234 FROM edw_local_system_parameters;
1235
1236 SELECT nls_language
1237 INTO l_nls_language
1238 FROM fnd_languages
1239 WHERE language_code = l_lang_code;
1240
1241 cid := DBMS_SQL.open_cursor;
1242 DBMS_SQL.parse (
1243 cid,
1244 'ALTER SESSION SET NLS_LANGUAGE = '
1245 || l_nls_language,
1246 DBMS_SQL.native
1247 );
1248 l_dummy_int := DBMS_SQL.EXECUTE (cid);
1249 put_debug_msg ( 'Set NLS language to '
1250 || l_nls_language);
1251 put_debug_msg ('********** End Procedure : set_wh_language');
1252 RETURN TRUE;
1253 EXCEPTION
1254 WHEN OTHERS
1255 THEN
1256 fnd_message.set_name ('BIS', 'EDW_NLS_LANG_SETUP');
1257 fnd_message.set_token ('VALUE', l_lang_code);
1258 edw_log.put_line (
1259 'Unable to set the language context to the warehouse language '
1260 || l_lang_code,FND_LOG.LEVEL_ERROR
1261 );
1262 RETURN FALSE;
1263 END set_wh_language;
1264
1265 FUNCTION is_instance_enabled
1266 RETURN BOOLEAN
1267 IS
1268 l_flag VARCHAR2 (10);
1269 l_stmt VARCHAR2 (1000);
1270 cv curtyp;
1271 l_status BOOLEAN;
1272 BEGIN
1273 put_debug_msg ('********** Start Function : is_instance_enabled');
1274
1275 IF (g_source_link IS NULL)
1276 OR (g_target_link IS NULL)
1277 THEN
1278 get_dblink_names (g_source_link, g_target_link);
1279 END IF;
1280
1281 l_stmt :=
1282 'SELECT enabled_flag
1283 FROM edw_source_instances_vl@'
1284 || g_target_link
1285 || '
1286 WHERE instance_code= ( SELECT instance_code
1287 FROM edw_local_instance)';
1288 put_debug_msg (
1289 'Checking whether instance is enabled in target database'
1290 );
1291 put_debug_msg ('Going to execute: ');
1292 put_debug_msg (l_stmt);
1293 OPEN cv FOR l_stmt;
1294 FETCH cv INTO l_flag;
1295 CLOSE cv;
1296
1297 IF (l_flag = 'Y')
1298 THEN
1299 l_status := TRUE;
1300 ELSE
1301 l_status := FALSE;
1302 END IF;
1303
1304 put_debug_msg ('********** End Function : is_instance_enabled');
1305 RETURN l_status;
1306 EXCEPTION
1307 WHEN NO_DATA_FOUND
1308 THEN
1309 g_errbuf := SQLERRM;
1310 g_retcode := SQLCODE;
1311 edw_log.put_line ('Error while checking whether source is enabled',FND_LOG.LEVEL_ERROR);
1312 edw_log.put_line ( g_errbuf
1313 || ':'
1314 || g_retcode,FND_LOG.LEVEL_ERROR);
1315 RETURN FALSE;
1316 END is_instance_enabled;
1317
1318 FUNCTION source_same_as_target
1319 RETURN BOOLEAN
1320 IS
1321 l_instance1 VARCHAR2 (200);
1322 l_instance2 VARCHAR2 (200);
1323 l_stmt VARCHAR2 (1000);
1324 cv curtyp;
1325 l_status BOOLEAN;
1326 BEGIN
1327 put_debug_msg (
1328 '********** Start Function : source_same_as_target to check EDW Installation'
1329 );
1330
1331 IF (g_source_link IS NULL)
1332 OR (g_target_link IS NULL)
1333 THEN
1334 get_dblink_names (g_source_link, g_target_link);
1335 END IF;
1336
1337 SELECT instance_code
1338 INTO l_instance1
1339 FROM edw_local_instance;
1340
1341 l_stmt := 'SELECT instance_code
1342 FROM edw_local_instance@'
1343 || g_target_link;
1344 put_debug_msg ('Running: ');
1345 put_debug_msg (l_stmt);
1346 OPEN cv FOR l_stmt;
1347 FETCH cv INTO l_instance2;
1348 CLOSE cv;
1349
1350 IF (l_instance1 = l_instance2)
1351 THEN
1352 put_debug_msg ('Single Instance is implemented.');
1353 l_status := TRUE;
1354 ELSE
1355 put_debug_msg (
1356 'Decoupled Architecture is implemented. Source Instance: '
1357 || l_instance1
1358 || '. Warehouse Instance: '
1359 || l_instance2
1360 );
1361 l_status := FALSE;
1362 END IF;
1363
1364 put_debug_msg (
1365 '********** End Function : source_same_as_target to check EDW Installation'
1366 );
1367 RETURN l_status;
1371 RETURN FALSE;
1368 EXCEPTION
1369 WHEN NO_DATA_FOUND
1370 THEN
1372 WHEN OTHERS
1373 THEN
1374 g_errbuf := SQLERRM;
1375 g_retcode := SQLCODE;
1376 edw_log.put_line (
1377 'Error while checking whether source same as target',FND_LOG.LEVEL_ERROR
1378 );
1379 edw_log.put_line ( g_errbuf
1380 || ':'
1381 || g_retcode,FND_LOG.LEVEL_ERROR);
1382 RAISE;
1383 END source_same_as_target;
1384
1385 PROCEDURE truncate_stg (p_tab_list IN tablist_type)
1386 IS
1387 l_stmt VARCHAR2 (1000);
1388 BEGIN
1389 put_debug_msg ('********** Start Procedure : truncate_stg');
1390
1391 IF p_tab_list.COUNT > 0
1392 THEN
1393 FOR i IN p_tab_list.FIRST .. p_tab_list.LAST
1394 LOOP
1395 l_stmt := 'TRUNCATE TABLE '
1396 || p_tab_list (i).tbl_owner
1397 || '.'
1398 || p_tab_list (i).tbl_name;
1399 put_debug_msg (
1400 'Truncate local staging table '
1401 || p_tab_list (i).tbl_owner
1402 || '.'
1403 || p_tab_list (i).tbl_name
1404 );
1405 EXECUTE IMMEDIATE l_stmt;
1406 END LOOP;
1407 END IF;
1408
1409 put_debug_msg ('********** End Procedure : truncate_stg');
1410 EXCEPTION
1411 WHEN OTHERS
1412 THEN
1413 g_errbuf := SQLERRM;
1414 g_retcode := SQLCODE;
1415 edw_log.put_line (
1416 'error while truncating local staging table '
1417 || g_errbuf
1418 || ':'
1419 || g_retcode,FND_LOG.LEVEL_ERROR
1420 );
1421 RAISE;
1422 END truncate_stg;
1423
1424 FUNCTION set_status_ready (p_tab_list IN tablist_type)
1425 RETURN NUMBER
1426 IS
1427 l_stmt VARCHAR2 (1000);
1428 l_count PLS_INTEGER := 0;
1429 l_num PLS_INTEGER := 0;
1430 l_rowcount PLS_INTEGER := 0;
1431 cv curtyp;
1432 l_stgtbl_name user_tables.table_name%TYPE;
1433 l_total_rows PLS_INTEGER := 0;
1434 BEGIN
1435 put_debug_msg ('********** Start Function : set_status_ready');
1436
1437 <<stgtbl_loop>>
1438 FOR i IN p_tab_list.FIRST .. p_tab_list.LAST
1439 LOOP
1440 l_stgtbl_name := p_tab_list (i).tbl_name;
1441 OPEN cv FOR 'select count(1) from '
1442 || l_stgtbl_name
1443 || ' where COLLECTION_STATUS = ''LOCAL READY'' ';
1444 FETCH cv INTO l_count;
1445 CLOSE cv;
1446
1447 IF l_count > 0
1448 THEN
1449 IF l_count > g_push_size AND g_push_size > 0
1450 THEN
1451 l_num := g_push_size;
1452 ELSE
1453 l_num := l_count;
1454 END IF;
1455
1456 l_stmt :=
1457 'UPDATE '
1458 || l_stgtbl_name
1459 || ' SET COLLECTION_STATUS = ''READY''
1460 WHERE COLLECTION_STATUS = ''LOCAL READY'' AND ROWNUM <= '
1461 || l_num;
1462 put_debug_msg ('Running: ');
1463 put_debug_msg (l_stmt);
1464 l_rowcount := 0;
1465
1466 <<update_loop>>
1467 FOR i IN 1 .. CEIL (l_count / l_num)
1468 LOOP
1469 EXECUTE IMMEDIATE l_stmt;
1470 l_rowcount := l_rowcount
1471 + SQL%ROWCOUNT;
1472 COMMIT;
1473 END LOOP update_loop;
1474
1475 edw_log.put_line (
1476 l_rowcount
1477 || ' rows for table '
1478 || l_stgtbl_name
1479 || ' proceeded.',FND_LOG.LEVEL_STATEMENT
1480 );
1481 END IF; -- l_count>0
1482
1483 l_total_rows := l_total_rows
1484 + l_rowcount;
1485 END LOOP stgtbl_loop;
1486
1487 put_debug_msg ('********** End Function : set_status_ready');
1488 RETURN (l_total_rows);
1489 EXCEPTION
1490 WHEN OTHERS
1491 THEN
1492 g_errbuf := SQLERRM;
1493 g_retcode := SQLCODE;
1494 edw_log.put_line ( 'set status failed'
1495 || g_errbuf
1496 || g_retcode,FND_LOG.LEVEL_ERROR);
1497 RETURN (-1);
1498 END set_status_ready;
1499
1500 FUNCTION get_last_push_date_logical (p_object_logical_name IN VARCHAR2)
1501 RETURN VARCHAR2
1502 IS
1503 l_obj_short_name VARCHAR2 (200);
1504 stmt VARCHAR2 (1000);
1505 cid INTEGER := 0;
1506 l_dummy INTEGER;
1507 l_date VARCHAR2 (30);
1508 BEGIN
1509 put_debug_msg (
1510 '********** Start Function : get_last_push_date_logical'
1511 );
1512
1513 /* bug 3256880*/
1514 IF (g_source_link IS NULL) OR (g_target_link IS NULL) THEN
1518 IF (p_object_logical_name IS NOT NULL)
1515 get_dblink_names (g_source_link, g_target_link);
1516 END IF;
1517
1519 THEN
1520 stmt := 'SELECT relation_name from edw_relations_md_v@'
1521 || g_target_link
1522 || ' where relation_long_name = :longname';
1523 put_debug_msg ('Running: ');
1524 put_debug_msg (stmt);
1525 cid := DBMS_SQL.open_cursor;
1526 DBMS_SQL.parse (cid, stmt, DBMS_SQL.native);
1527 DBMS_SQL.bind_variable (cid, ':longname', p_object_logical_name);
1528 DBMS_SQL.define_column (cid, 1, l_obj_short_name, 100);
1529 l_dummy := DBMS_SQL.execute_and_fetch (cid);
1530 DBMS_SQL.column_value (cid, 1, l_obj_short_name);
1531 DBMS_SQL.close_cursor (cid);
1532 l_date := get_last_push_date (l_obj_short_name);
1533 ELSE
1534 l_date := NULL;
1535 END IF;
1536
1537 put_debug_msg ('********** End Function : get_last_push_date_logical');
1538 RETURN l_date;
1539 END get_last_push_date_logical;
1540
1541 FUNCTION get_last_push_date (p_object IN VARCHAR2)
1542 RETURN VARCHAR2
1543 IS
1544 l_date DATE;
1545 l_datedt VARCHAR2 (100);
1546 l_cid INTEGER;
1547 l_stmt VARCHAR2 (1000);
1548 l_dummy INTEGER;
1549 BEGIN
1550 put_debug_msg ('********** Start Function : get_last_push_date');
1551
1552
1553 /* l_stmt := ' ALTER SESSION SET global_names = false';
1554 l_cid := DBMS_SQL.OPEN_CURSOR;
1555 DBMS_SQL.PARSE(l_cid, l_stmt, DBMS_SQL.NATIVE);
1556 l_dummy := DBMS_SQL.EXECUTE(l_cid);
1557 DBMS_SQL.CLOSE_CURSOR(l_cid); */
1558 --edw_misc_util.globalnamesoff;
1559
1560 IF (g_source_link IS NULL)
1561 OR (g_target_link IS NULL)
1562 THEN
1563 get_dblink_names (g_source_link, g_target_link);
1564 END IF;
1565
1566 l_stmt :=
1567 ' select nvl(period_end, to_date(''01/01/1950'',''MM/DD/YYYY'')) '
1568 || ' from edw_push_detail_log@'
1569 || g_target_link
1570 || ' where object_name= :s and push_status=''SUCCESS'' and '
1571 || ' instance_code=(select instance_code from edw_local_instance)'
1572 || ' and last_update_date= ( select max(last_update_date)
1573 from edw_push_detail_log@'
1574 || g_target_link
1575 || ' where object_name=:s and push_status=''SUCCESS'' and '
1576 || ' instance_code=(select instance_code from edw_local_instance))';
1577 put_debug_msg ('Running: ');
1578 put_debug_msg (l_stmt);
1579 l_cid := DBMS_SQL.open_cursor;
1580 DBMS_SQL.parse (l_cid, l_stmt, DBMS_SQL.native);
1581 DBMS_SQL.bind_variable (l_cid, ':s', p_object);
1582 DBMS_SQL.define_column (l_cid, 1, l_date);
1583 l_dummy := DBMS_SQL.EXECUTE (l_cid);
1584
1585 IF DBMS_SQL.fetch_rows (l_cid) <> 0
1586 THEN
1587 DBMS_SQL.column_value (l_cid, 1, l_date);
1588 END IF;
1589
1590 DBMS_SQL.close_cursor (l_cid);
1591 l_date := NVL (l_date, TO_DATE ('01/01/1950', 'MM/DD/YYYY'));
1592 l_datedt := fnd_date.date_to_displaydt (l_date);
1593 put_debug_msg ('********** End Function : get_last_push_date');
1594 RETURN l_datedt;
1595 END get_last_push_date;
1596
1597 PROCEDURE get_dblink_names (
1598 x_source_link OUT NOCOPY VARCHAR2,
1599 x_target_link OUT NOCOPY VARCHAR2
1600 )
1601 IS
1602 CURSOR c_global_names_opt
1603 IS
1604 SELECT param.VALUE
1605 FROM v$parameter param
1606 WHERE param.NAME = 'global_names';
1607
1608 CURSOR c_global_names_val
1609 IS
1610 SELECT GLOBAL_NAME val
1611 FROM GLOBAL_NAME;
1612
1613 l_gname_set v$parameter.VALUE%TYPE;
1614 l_gname_val GLOBAL_NAME.GLOBAL_NAME%TYPE;
1615 l_source_link VARCHAR2 (128);
1616 l_target_link VARCHAR2 (128);
1617 BEGIN
1618 put_debug_msg ('********** Start Procedure : get_dblink_names');
1619 put_debug_msg ('Profile Options EDW_APPS_TO_APPS and EDW_APPS_TO_WH');
1620 OPEN c_global_names_opt;
1621 FETCH c_global_names_opt INTO l_gname_set;
1622 CLOSE c_global_names_opt;
1623 l_source_link := fnd_profile.VALUE ('EDW_APPS_TO_APPS');
1624 l_target_link := fnd_profile.VALUE ('EDW_APPS_TO_WH');
1625
1626 IF l_gname_set = 'TRUE'
1627 THEN
1628 OPEN c_global_names_val;
1629 FETCH c_global_names_val INTO l_gname_val;
1630 CLOSE c_global_names_val;
1631 put_debug_msg ('l_source_link='||l_source_link);
1632 put_debug_msg ('l_target_link='||l_target_link);
1633 put_debug_msg ('l_gname_val='||l_gname_val);
1634 --bug 3358820
1635 --IF (l_source_link IS NULL)
1636 -- OR (l_source_link <> l_gname_val)
1637 --OR (l_target_link IS NULL)
1638 if (l_source_link IS NULL) or (l_target_link IS NULL) THEN
1639 RAISE g_push_remote_failure;
1640 elsif instr(l_source_link,'@')>0 then
1641 if upper(substr(l_source_link,1,instr(l_source_link,'@')-1))<>upper(l_gname_val) then
1642 RAISE g_push_remote_failure;
1646 RAISE g_push_remote_failure;
1643 end if;
1644 else
1645 if upper(l_source_link)<>upper(l_gname_val) then
1647 end if;
1648 end if;
1649 ELSIF l_gname_set = 'FALSE'
1650 AND ( l_source_link IS NULL
1651 OR l_target_link IS NULL
1652 )
1653 THEN
1654 l_source_link := 'APPS_TO_APPS';
1655 l_target_link := 'EDW_APPS_TO_WH';
1656 END IF;
1657
1658 put_debug_msg ( 'Loop back link is '
1659 || l_source_link);
1660 put_debug_msg ( 'Warehouse link is '
1661 || l_target_link);
1662 put_debug_msg ('********** End Procedure : get_dblink_names');
1663 x_source_link := l_source_link;
1664 x_target_link := l_target_link;
1665 EXCEPTION
1666 WHEN g_push_remote_failure
1667 THEN
1668 g_errbuf :=
1669 'GLOBAL NAMES option is enabled.
1670 Database Link Names are not set corretly in Application Profile Option.
1671 EDW: Loop back link name is'
1672 || l_source_link
1673 || '. '
1674 || 'EDW: Runtime link name is '
1675 || l_target_link
1676 || '. Database Global Name is '
1677 || l_gname_val;
1678 g_retcode := 'EDW: ';
1679 edw_log.put_line ( 'error '
1680 || g_retcode
1681 || ':'
1682 || g_errbuf,FND_LOG.LEVEL_ERROR);
1683 edw_log.put_line (
1684 'Data migration from local to remote staging have failed',FND_LOG.LEVEL_ERROR
1685 );
1686 RAISE;
1687 WHEN OTHERS
1688 THEN
1689 g_errbuf := SQLERRM;
1690 g_retcode := SQLCODE;
1691 edw_log.put_line ( 'error '
1692 || g_retcode
1693 || ':'
1694 || g_errbuf,FND_LOG.LEVEL_ERROR);
1695 edw_log.put_line (
1696 'Data migration from local to remote staging have failed',FND_LOG.LEVEL_ERROR
1697 );
1698 RAISE;
1699 END get_dblink_names;
1700
1701 PROCEDURE clean_up (p_tab_list IN tablist_type, suffix IN VARCHAR2)
1702 IS
1703 dummy PLS_INTEGER;
1704 l_tmp_table_name user_tables.table_name%TYPE;
1705 l_suffix_length VARCHAR2 (20);
1706
1707 CURSOR c_check_tbl (p_tbl_name IN VARCHAR2)
1708 IS
1709 SELECT 1
1710 FROM dba_tables
1711 WHERE table_name = p_tbl_name AND owner = g_bis_schema;
1712 BEGIN
1713 put_debug_msg ('********** Start Procedure : clean_up');
1714
1715 IF tab_stglist.COUNT > 0
1716 THEN
1717 put_debug_msg (
1718 'Calling edw_collection_util.clean_up to drop temporary tables:'
1719 );
1720 l_suffix_length := LENGTH (suffix);
1721
1722 FOR i IN tab_stglist.FIRST .. tab_stglist.LAST
1723 LOOP
1724 put_debug_msg (
1725 g_bis_schema
1726 || '.'
1727 || SUBSTR (
1728 tab_stglist (i).tbl_name,
1729 0,
1730 30
1731 - l_suffix_length
1732 )
1733 || suffix
1734 );
1735 END LOOP;
1736
1737 FOR i IN tab_stglist.FIRST .. tab_stglist.LAST
1738 LOOP
1739 l_tmp_table_name := SUBSTR (
1740 tab_stglist (i).tbl_name,
1741 0,
1742 30
1743 - l_suffix_length
1744 )
1745 || suffix;
1746 OPEN c_check_tbl (l_tmp_table_name);
1747 FETCH c_check_tbl INTO dummy;
1748
1749 IF c_check_tbl%FOUND
1750 THEN
1751 EXECUTE IMMEDIATE 'drop table '
1752 || g_bis_schema
1753 || '.'
1754 || l_tmp_table_name;
1755 END IF;
1756
1757 CLOSE c_check_tbl;
1758 END LOOP;
1759 END IF; -- count >0
1760
1761 put_debug_msg ('********** End Procedure : clean_up');
1762 EXCEPTION
1763 WHEN OTHERS
1764 THEN
1765 RAISE;
1766 END clean_up;
1767
1768 PROCEDURE get_stg_table_names (
1769 p_object_name IN VARCHAR2,
1770 tablist OUT NOCOPY tablist_type
1771 )
1772 IS
1773 l_smt VARCHAR2 (2000);
1774 l_len PLS_INTEGER := 0;
1775 cv curtyp;
1776 empty_table tablist_type;
1777 BEGIN
1778 put_debug_msg (
1779 '********** Calling Get_stg_table_names to get the list of staging tables for object '
1780 || p_object_name
1781 );
1782
1783 IF g_transport_data
1784 THEN
1788 tab_stglist := empty_table;
1785 IF get_object_type (p_object_name)
1786 THEN
1787 -- assign to global variables, add log
1789
1790 IF g_object_type = edw_dim
1791 THEN
1792 l_smt :=
1793 'SELECT stg.relation_name FROM edw_levels_md_v@'
1794 || g_target_link
1795 || ' ltc, edw_relationmapping_md_v@'
1796 || g_target_link
1797 || ' map, edw_relations_md_v@'
1798 || g_target_link
1799 || ' stg WHERE ltc.dim_name = :a'
1800 || ' AND map.targetdataentity = ltc.level_table_id'
1801 || ' AND map.sourcedataentity = stg.relation_id'
1802 || ' AND ltc.level_table_name = ltc.level_name||''_LTC''';
1803 ELSIF g_object_type = edw_fact
1804 THEN
1805 l_smt := 'SELECT stg.relation_name FROM edw_relations_md_v@'
1806 || g_target_link
1807 || ' stg, edw_relationmapping_md_v@'
1808 || g_target_link
1809 || ' map, edw_facts_md_v@'
1810 || g_target_link
1811 || ' fact WHERE fact.fact_name = :a'
1812 || ' AND map.targetdataentity = fact.fact_id'
1813 || ' AND stg.relation_id = map.sourcedataentity';
1814 END IF;
1815
1816 put_debug_msg ('Running: ');
1817 put_debug_msg (l_smt);
1818 OPEN cv FOR l_smt USING p_object_name;
1819
1820 LOOP
1821 FETCH cv INTO tablist (l_len).tbl_name;
1822 EXIT WHEN cv%NOTFOUND;
1823 tablist (l_len).tbl_owner :=
1824 get_syn_info (tablist (l_len).tbl_name);
1825 l_len := l_len
1826 + 1;
1827 END LOOP;
1828
1829 CLOSE cv;
1830 put_debug_msg (
1831 'Staging Tables List for EDW object: '
1832 || p_object_name
1833 );
1834 put_debug_msg ('------------------------------');
1835
1836 IF tablist.COUNT > 0
1837 THEN
1838 FOR i IN tablist.FIRST .. tablist.LAST
1839 LOOP
1840 put_debug_msg (tablist (i).tbl_name);
1841 END LOOP;
1842 END IF;
1843 ELSE
1844 NULL;
1845 -- RAISE g_push_remote_failure;
1846 END IF;
1847 END IF; -- g_transport_data
1848
1849 put_debug_msg ('********** End Procedure : get_stg_table_names'); -- get_object_type
1850 EXCEPTION
1851 WHEN OTHERS
1852 THEN
1853 tablist.delete;
1854 RAISE;
1855 END get_stg_table_names;
1856
1857 FUNCTION push_to_target
1858 RETURN NUMBER
1859 IS
1860 cv curtyp;
1861 l_stmt VARCHAR2 (30000);
1862 l_column all_tab_columns.column_name%TYPE;
1863 l_collist VARCHAR2 (10000);
1864 l_scollist VARCHAR2 (10000);
1865 l_edw_extent PLS_INTEGER := 0;
1866 row_count PLS_INTEGER := 0;
1867 l_rowcount INTEGER := 0;
1868 l_count INTEGER := 0;
1869 l_colcount PLS_INTEGER := 0;
1870 l_pushcount PLS_INTEGER := 0;
1871 l_tmptbl_name user_tables.table_name%TYPE;
1872 l_stgtbl_name user_tables.table_name%TYPE;
1873 l_optbl_name user_tables.table_name%TYPE;
1874 l_stg_owner user_users.username%TYPE;
1875 l_stg_initial_extent PLS_INTEGER := 0;
1876 l_stg_next_extent PLS_INTEGER := 0;
1877 l_partitioned_table user_tables.partitioned%TYPE; -- added for bug 4300166
1878 BEGIN
1879 put_debug_msg ('********** Start Procedure : push_to_target');
1880 put_timestamp;
1881
1882 IF tab_stglist.COUNT > 0
1883 THEN
1884 IF g_source_same_as_target = TRUE
1885 THEN
1886 put_debug_msg ('Source Instance the same as Target.');
1887
1888 -- Single Instance Case
1889 -----------------------
1890 -- Update Local Staging Table in case of single instance
1891
1892 l_count := set_status_ready (tab_stglist);
1893
1894 IF (l_count = -1)
1895 THEN
1896 edw_log.put_line (
1897 'Update of table'
1898 || l_stgtbl_name
1899 || ' failed',FND_LOG.LEVEL_ERROR
1900 );
1901 ELSE
1902 edw_log.put_line ( l_count
1903 || ' total rows proceeded.',FND_LOG.LEVEL_STATEMENT);
1904 edw_log.put_line (' ',FND_LOG.LEVEL_STATEMENT);
1905 END IF;
1906 ELSE
1907
1908 -- Decoupled Architecture Case
1909 ------------------------------
1910 put_debug_msg ('Source different from Target. Push to remote.');
1911
1915 USING g_bis_schema;
1912 IF g_op_tablespace IS NULL
1913 THEN
1914 OPEN cv FOR 'select default_tablespace from dba_users where username=:u'
1916 FETCH cv INTO g_op_tablespace;
1917 CLOSE cv;
1918 END IF;
1919
1920 IF l_edw_extent = 0
1921 THEN
1922 OPEN cv FOR 'select initial_extent from dba_tablespaces where tablespace_name= :t'
1923 USING g_op_tablespace;
1924 FETCH cv INTO l_edw_extent;
1925 CLOSE cv;
1926 END IF;
1927
1928 put_debug_msg ( 'Operational Tablespace: '
1929 || g_op_tablespace);
1930 put_debug_msg ( 'Temporary Table extent size: '
1931 || l_edw_extent);
1932
1933
1934 -- Start Loop for EDW Object Staging Tables
1935 <<stgtbl_loop>>
1936 FOR i IN tab_stglist.FIRST .. tab_stglist.LAST
1937 LOOP
1938 l_count := 0;
1939 l_stgtbl_name := tab_stglist (i).tbl_name;
1940 l_stg_owner := tab_stglist (i).tbl_owner;
1941 l_tmptbl_name :=
1942 SUBSTR (tab_stglist (i).tbl_name, 0, 27)
1943 || '_SK';
1944 l_optbl_name :=
1945 SUBSTR (tab_stglist (i).tbl_name, 0, 27)
1946 || '_SL';
1947
1948 -- Check data availability in Local Staging Table
1949 OPEN cv FOR 'select count(1) from '
1950 || l_stgtbl_name
1951 || ' where collection_status in (''READY'',''LOCAL READY'')';
1952 FETCH cv INTO row_count;
1953 CLOSE cv;
1954 put_debug_msg (
1955 'Check data for data in table '
1956 || l_stgtbl_name
1957 || ' Row Count: '
1958 || row_count
1959 );
1960
1961
1962 -- If there is data in Local Staging table then run transportation process
1963 IF row_count > 0
1964 THEN
1965
1966 -- Get list of columns
1967 l_colcount := 0;
1968 l_stmt :=
1969 'SELECT column_name FROM all_tab_columns WHERE table_name =:t AND column_name not in ( ''COLLECTION_STATUS'',''REQUEST_ID'')'
1970 || ' AND owner = :o';
1971 put_debug_msg ( 'Running for '
1972 || l_stgtbl_name);
1973 put_debug_msg (l_stmt);
1974 l_collist := NULL;
1975 l_scollist := NULL;
1976 OPEN cv FOR l_stmt USING l_stgtbl_name, l_stg_owner;
1977
1978 <<table_column_loop>>
1979 LOOP
1980 FETCH cv INTO l_column;
1981 EXIT table_column_loop WHEN cv%NOTFOUND;
1982 put_debug_msg (l_column);
1983
1984 IF (l_colcount = 0)
1985 THEN
1986 l_collist := l_collist
1987 || l_column;
1988 l_scollist := l_scollist
1989 || 's.'
1990 || l_column;
1991 l_colcount := l_colcount
1992 + 1;
1993 ELSE
1994 l_collist := l_collist
1995 || ', '
1996 || l_column;
1997 l_scollist := l_scollist
1998 || ', '
1999 || 's.'
2000 || l_column;
2001 END IF;
2002 END LOOP table_column_loop;
2003
2004 CLOSE cv;
2005
2006
2007 -- End of fetching table columns
2008
2009
2010 IF row_count > g_push_size AND g_push_size > 0
2011 THEN
2012 put_debug_msg (
2013 'Starting Data Transportation Process using Temporary Tables '
2014 );
2015 put_timestamp;
2016 --partitioned column added for bug 4300166
2017 OPEN cv FOR 'select initial_extent, next_extent, partitioned from dba_tables where table_name= :t and owner =:o'
2018 USING l_stgtbl_name, l_stg_owner;
2019 FETCH cv INTO l_stg_initial_extent, l_stg_next_extent,l_partitioned_table;
2020 CLOSE cv;
2021
2022 --code added for bug fix 4300166
2023 ----case 1 partitioned table, both intial and next extent are null in dba tables
2024 IF((l_stg_initial_extent is null and l_stg_next_extent is null)
2025 and l_partitioned_table = 'YES') THEN
2026 l_stg_initial_extent := l_edw_extent;
2027 l_stg_next_extent := l_edw_extent;
2028 ELSE IF(l_stg_initial_extent is not null and l_stg_next_extent is null) THEN
2029 ---case 2 locally managed tablespaces, next extent can be null in dba_tables
2033
2030 l_stg_next_extent := l_stg_initial_extent;
2031 END IF;
2032 END IF;
2034 put_debug_msg (
2035 'Storage Parameters for OP Table: '
2036 || l_optbl_name
2037 );
2038 put_debug_msg (
2039 'INITIAL Extent: '
2040 || l_stg_initial_extent
2041 );
2042 put_debug_msg ( 'NEXT Extent: '
2043 || l_stg_next_extent);
2044 l_pushcount := 1;
2045 -- multiple iterations case
2046 -- create table for temporary data
2047
2048 set_transaction_rbs (g_rbs);
2049 l_stmt :=
2050 'create table '
2051 || g_bis_schema
2052 || '.'
2053 || l_tmptbl_name
2054 || ' tablespace '
2055 || g_op_tablespace
2056 || ' storage ( initial '
2057 || l_edw_extent
2058 || ' next '
2059 || l_edw_extent
2060 || ' PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645) '
2061 || ' as select /*+PARALLEL('
2062 || l_stgtbl_name
2063 || ', '
2064 || g_parallel
2065 || ') */ rowid row_id, 0 status from '
2066 || l_stgtbl_name
2067 || ' where collection_status in (''READY'', ''LOCAL READY'') ';
2068 put_timestamp;
2069 put_debug_msg (
2070 'Level STAGING Table: '
2071 || l_stgtbl_name
2072 );
2073 put_debug_msg ('Running: ');
2074 put_debug_msg (l_stmt);
2075 EXECUTE IMMEDIATE l_stmt;
2076
2077 /* move data from local interface table into remote interface
2078 table across db link using tables with temporary data */
2079
2080 <<bach_insert_loop>>
2081 WHILE l_pushcount <= CEIL (row_count / g_push_size)
2082 LOOP
2083 set_transaction_rbs (g_rbs);
2084 l_stmt :=
2085 'update '
2086 || g_bis_schema
2087 || '.'
2088 || l_tmptbl_name
2089 || ' set status =1 where status = 0 and rownum <='
2090 || g_push_size;
2091 put_timestamp;
2092 put_debug_msg ('Running: ');
2093 put_debug_msg (l_stmt);
2094 EXECUTE IMMEDIATE l_stmt;
2095 COMMIT;
2096 put_timestamp;
2097 set_transaction_rbs (g_rbs);
2098 l_stmt :=
2099 'create table '
2100 || g_bis_schema
2101 || '.'
2102 || l_optbl_name
2103 || ' tablespace '
2104 || g_op_tablespace
2105 || ' storage ( initial '
2106 || l_stg_initial_extent
2107 || ' next '
2108 || l_stg_next_extent
2109 || ' PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645) '
2110 || ' as select /*+ORDERED PARALLEL('
2111 || l_stgtbl_name
2112 || ', '
2113 || g_parallel
2114 || ') PARALLEL('
2115 || g_bis_schema
2116 || '.'
2117 || l_tmptbl_name
2118 || ', '
2119 || g_parallel
2120 || ') '
2121 || '*/ ''READY'' COLLECTION_STATUS, '
2122 || g_request_id
2123 || ' REQUEST_ID, '
2124 || l_scollist
2125 || ' from '
2126 || g_bis_schema
2127 || '.'
2128 || l_tmptbl_name
2129 || ' t, '
2130 || l_stgtbl_name
2131 || ' s where t.row_id = s.rowid and t.status = 1 ';
2132 put_timestamp;
2133 put_debug_msg (
2134 'Level STAGING Table: '
2135 || l_stgtbl_name
2136 );
2137 put_debug_msg ('Running: ');
2138 put_debug_msg (l_stmt);
2139 EXECUTE IMMEDIATE l_stmt;
2140 put_timestamp;
2144 l_stmt := 'insert /*+APPEND PARALLEL('
2141 -- Insert data accross db link
2142
2143 set_transaction_rbs (g_rbs);
2145 || l_stgtbl_name
2146 || '@'
2147 || g_target_link
2148 || ', '
2149 || g_parallel
2150 || ') '
2151 || '*/ into '
2152 || l_stgtbl_name
2153 || '@'
2154 || g_target_link
2155 || ' (COLLECTION_STATUS, REQUEST_ID, '
2156 || l_collist
2157 || ') '
2158 || ' select /*+PARALLEL('
2159 || g_bis_schema
2160 || '.'
2161 || l_optbl_name
2162 || ', '
2163 || g_parallel
2164 || ')*/'
2165 || ' COLLECTION_STATUS, REQUEST_ID, '
2166 || l_collist
2167 || ' FROM '
2168 || g_bis_schema
2169 || '.'
2170 || l_optbl_name;
2171 put_debug_msg ('Running: ');
2172 put_debug_msg (l_stmt);
2173 EXECUTE IMMEDIATE l_stmt;
2174 put_timestamp;
2175 l_count := l_count
2176 + SQL%ROWCOUNT;
2177 COMMIT;
2178
2179 -- drop second OP table
2180
2181 set_transaction_rbs (g_rbs);
2182 l_stmt := 'drop table '
2183 || g_bis_schema
2184 || '.'
2185 || l_optbl_name;
2186 put_debug_msg ('Running: ');
2187 put_debug_msg (l_stmt);
2188 EXECUTE IMMEDIATE l_stmt;
2189 put_timestamp;
2190
2191 -- Update first OP table
2192 set_transaction_rbs (g_rbs);
2193 l_stmt := 'update '
2194 || g_bis_schema
2195 || '.'
2196 || l_tmptbl_name
2197 || ' set status =2 where status = 1';
2198 put_debug_msg ('Running: ');
2199 put_debug_msg (l_stmt);
2200 EXECUTE IMMEDIATE l_stmt;
2201 COMMIT;
2202 put_timestamp;
2203 put_debug_msg (
2204 'Iteration '
2205 || l_pushcount
2206 || ' Completed'
2207 );
2208 l_pushcount := l_pushcount
2209 + 1;
2210 END LOOP bach_insert_loop;
2211
2212 put_timestamp;
2213
2214 -- One Iteration Case
2215 ELSIF (row_count <= g_push_size AND g_push_size > 0)
2216 OR (g_push_size = 0)
2217 OR (g_push_size IS NULL)
2218 THEN
2219 -- insert data into remote interface table
2220
2221 put_debug_msg (
2222 'Direct insert into Remote Staging Tables. '
2223 );
2224 put_timestamp;
2225 set_transaction_rbs (g_rbs);
2226 l_stmt :=
2227 'insert into '
2228 || l_stgtbl_name
2229 || '@'
2230 || g_target_link
2231 || ' (COLLECTION_STATUS, REQUEST_ID, '
2232 || l_collist
2233 || ') /*+APPEND PARALLEL('
2234 || l_stgtbl_name
2235 || '@'
2236 || g_target_link
2237 || ', '
2238 || g_parallel
2239 || ')*/ '
2240 || ' select /*+PARALLEL('
2241 || l_stgtbl_name
2242 || ', '
2243 || g_parallel
2244 || ')*/ ''READY'','
2245 || g_request_id
2246 || ','
2247 || l_scollist
2248 || ' FROM '
2249 || l_stgtbl_name
2250 || ' s '
2251 || ' where s.COLLECTION_STATUS in (''READY'',''LOCAL READY'')';
2252 put_debug_msg ('Running: ');
2253 put_debug_msg (l_stmt);
2254 EXECUTE IMMEDIATE l_stmt;
2255 COMMIT;
2259 END IF; -- row_count>0 There is data in LSTG tables
2256 put_timestamp;
2257 l_count := SQL%ROWCOUNT;
2258 END IF; -- end iteration cases
2260
2261 edw_log.put_line (
2262 l_count
2263 || ' rows for table '
2264 || l_stgtbl_name
2265 || ' proceeded.',FND_LOG.LEVEL_STATEMENT
2266 );
2267 edw_log.put_line (' ',FND_LOG.LEVEL_STATEMENT);
2268 l_rowcount := l_rowcount
2269 + l_count;
2270 END LOOP stgtbl_loop; -- end loop for table list
2271 END IF;
2272 -- end check of EDW Architecture: single instance or decoupled implementation
2273 END IF; -- check number of stg tables
2274
2275 IF g_source_same_as_target = FALSE
2276 THEN
2277 edw_log.put_line ( 'Total rows proceeded.'
2278 || l_rowcount,FND_LOG.LEVEL_STATEMENT);
2279 edw_log.put_line (' ',FND_LOG.LEVEL_STATEMENT);
2280 ELSIF l_rowcount = 0 AND g_source_same_as_target = TRUE
2281 THEN
2282 edw_log.put_line (
2283 'Data for Object'
2284 || g_object_name
2285 || 'collected. ',FND_LOG.LEVEL_PROCEDURE
2286 );
2287 edw_log.put_line (' ',FND_LOG.LEVEL_PROCEDURE);
2288 ELSIF l_rowcount > 0 AND g_source_same_as_target = TRUE
2289 THEN
2290 edw_log.put_line (
2291 'Data for Object'
2292 || g_object_name
2293 || 'collected. ',FND_LOG.LEVEL_PROCEDURE
2294 );
2295 edw_log.put_line ( l_rowcount
2296 || ' rows are updated',FND_LOG.LEVEL_PROCEDURE);
2297 edw_log.put_line (' ',FND_LOG.LEVEL_PROCEDURE);
2298 END IF;
2299
2300 put_debug_msg ('********** End Procedure : push_to_target');
2301 put_timestamp;
2302 RETURN (l_rowcount);
2303 EXCEPTION
2304 WHEN OTHERS
2305 THEN
2306 ROLLBACK;
2307 g_errbuf := SQLERRM;
2308 g_retcode := SQLCODE;
2309 edw_log.put_line (
2310 'EDW_COLLECTION_UTIL.push_to_target error while pushing to target',FND_LOG.LEVEL_ERROR
2311 );
2312 edw_log.put_line ( g_retcode
2313 || ' : '
2314 || g_errbuf,FND_LOG.LEVEL_ERROR);
2315 RETURN (-1);
2316 END push_to_target;
2317
2318 FUNCTION get_syn_info (syn_name IN VARCHAR2)
2319 RETURN VARCHAR2
2320 IS
2321 TYPE cv_type IS REF CURSOR;
2322
2323 c_get_tbl_owner cv_type;
2324 l_tbl_owner user_users.username%TYPE;
2325 BEGIN
2326 put_debug_msg ('********** Start Function : get_syn_info');
2327 -- bug 4300166 underscore added to supress GSCC error
2328 put_debug_msg ( 'Reading synonym _APPS_.'
2329 || syn_name
2330 || ' information');
2331 OPEN c_get_tbl_owner FOR '
2332 select table_owner from user_synonyms where synonym_name = :s'
2333 USING syn_name;
2334
2335 FETCH c_get_tbl_owner INTO l_tbl_owner;
2336
2337 IF c_get_tbl_owner%NOTFOUND
2338 THEN
2339 -- bug 4300166 underscore added to supress GSCC error
2340 edw_log.put_line (
2341 'Error. Synonym _APPS_.'
2342 || syn_name
2343 || ' is invalid.',FND_LOG.LEVEL_ERROR
2344 );
2345 edw_log.put_line (' ');
2346 RAISE g_push_remote_failure;
2347 END IF;
2348
2349 CLOSE c_get_tbl_owner;
2350 put_debug_msg ('********** End Function : get_syn_info');
2351 RETURN l_tbl_owner;
2352 END get_syn_info;
2353
2354 PROCEDURE put_timestamp
2355 IS
2356 BEGIN
2357 IF g_debug
2358 THEN
2359 edw_log.put_line (fnd_date.date_to_displaydt (SYSDATE),FND_LOG.LEVEL_STATEMENT);
2360 edw_log.put_line (' ',FND_LOG.LEVEL_STATEMENT);
2361 END IF;
2362 END put_timestamp;
2363
2364 PROCEDURE put_debug_msg (p_message IN VARCHAR2)
2365 IS
2366 BEGIN
2367 IF g_debug
2368 THEN
2369 edw_log.put_line (p_message,FND_LOG.LEVEL_STATEMENT);
2370 edw_log.put_line (' ',FND_LOG.LEVEL_STATEMENT);
2371 END IF;
2372 END put_debug_msg;
2373
2374 FUNCTION is_object_for_local_load (p_object_name IN VARCHAR2)
2375 RETURN BOOLEAN
2376 IS
2377 l_stmt VARCHAR2 (2000);
2378 l_res INTEGER;
2379 l_status BOOLEAN;
2380
2381 TYPE curtyp IS REF CURSOR;
2382
2383 cv curtyp;
2384 BEGIN
2385 l_stmt :=
2386 'select 1 from FND_COMMON_LOOKUPS where lookup_type=:a and lookup_code=:b';
2387 OPEN cv FOR l_stmt USING 'EDW_OBJECTS_TO_LOAD', p_object_name;
2388 FETCH cv INTO l_res;
2389 CLOSE cv;
2390
2391 IF l_res = 1
2392 THEN
2393 l_status := TRUE;
2394 ELSE
2395 l_status := FALSE;
2396 END IF;
2397
2398 RETURN l_status;
2399 EXCEPTION
2400 WHEN OTHERS
2401 THEN
2402 edw_log.put_line (SQLERRM,FND_LOG.LEVEL_ERROR);
2403 RAISE;
2404 END is_object_for_local_load;
2405
2406 PROCEDURE set_transaction_rbs (p_rbs IN VARCHAR2)
2407 IS
2408 l_stmt VARCHAR2 (1000);
2409 BEGIN
2410 IF p_rbs IS NOT NULL
2411 THEN
2412 l_stmt := 'set transaction use rollback segment '
2413 || p_rbs;
2414 EXECUTE IMMEDIATE l_stmt;
2415 END IF;
2416 END set_transaction_rbs;
2417 END edw_collection_util;
2418