[Home] [Help]
PACKAGE BODY: APPS.PV_CONTEXT_VALUES
Source
1 package body PV_CONTEXT_VALUES AS
2 /* $Header: pvxvconb.pls 120.12 2006/01/13 10:00:46 pklin noship $ */
3
4
5
6 /*************************************************************************************/
7 /* */
8 /* */
9 /* */
10 /* Global Variable Declaration */
11 /* */
12 /* */
13 /* */
14 /*************************************************************************************/
15 g_RETCODE VARCHAR2(10) := '0';
16 g_common_currency VARCHAR2(15);
17 g_module_name VARCHAR2(48);
18 g_batch_insert_count NUMBER;
19 g_non_batch_insert_count NUMBER;
20 g_partner_temp_table VARCHAR2(30) := 'PV_PARTNER_ID_SESSION';
21 g_log_to_file VARCHAR2(5) := 'Y';
22
23 g_apps_schema VARCHAR2(30);
24
25
26 TYPE r_func_perf_attrs_rec IS RECORD (
27 performance_flag VARCHAR2(1),
28 attribute_type VARCHAR2(30),
29 return_type VARCHAR2(30),
30 sql_text VARCHAR2(2000)
31 );
32
33 TYPE t_func_perf_attrs_tbl IS TABLE OF r_func_perf_attrs_rec
34 INDEX BY BINARY_INTEGER;
35
36 g_func_perf_attrs_tbl t_func_perf_attrs_tbl;
37 g_func_perf_attrs_empty t_func_perf_attrs_tbl;
38
39
40 /*************************************************************************************/
41 /* */
42 /* */
43 /* */
44 /* Exceptions to Catch */
45 /* */
46 /* */
47 /* */
48 /*************************************************************************************/
49 g_index_columns_existed EXCEPTION;
50 PRAGMA EXCEPTION_INIT(g_index_columns_existed, -1408);
51
52 -- -----------------------------------------------------
53 -- ORA-00955: name is already used by an existing object
54 -- -----------------------------------------------------
55 g_name_already_used EXCEPTION;
56 PRAGMA EXCEPTION_INIT(g_name_already_used, -955);
57
58 g_e_invalid_sql EXCEPTION;
59 PRAGMA EXCEPTION_INIT(g_e_invalid_sql, -900);
60
61 g_e_undeclared_identifier EXCEPTION;
62 PRAGMA EXCEPTION_INIT(g_e_undeclared_identifier, -6550);
63
64 g_e_definer_rights EXCEPTION;
65 PRAGMA EXCEPTION_INIT(g_e_definer_rights, -31603);
66
67
68 -- -----------------------------------------------------
69 -- ORA-00904: invalid column name
70 -- -----------------------------------------------------
71 g_e_invliad_column_name EXCEPTION;
72 PRAGMA EXCEPTION_INIT(g_e_invliad_column_name, -904);
73
74 -- -----------------------------------------------------
75 -- ORA-01476: divisor is equal to zero
76 -- -----------------------------------------------------
77 g_e_divisor_is_zero EXCEPTION;
78 PRAGMA EXCEPTION_INIT(g_e_divisor_is_zero, -1476);
79
80
81 /*************************************************************************************/
82 /* */
83 /* */
84 /* */
85 /* private procedure declaration */
86 /* */
87 /* */
88 /* */
89 /*************************************************************************************/
90 PROCEDURE Debug(
91 p_msg_string IN VARCHAR2,
92 p_msg_type IN VARCHAR2 := 'PV_DEBUG_MESSAGE'
93 );
94
95
96 PROCEDURE Set_Message(
97 p_msg_level IN NUMBER,
98 p_msg_name IN VARCHAR2,
99 p_token1 IN VARCHAR2 := NULL,
100 p_token1_value IN VARCHAR2 := NULL,
101 p_token2 IN VARCHAR2 := NULL,
102 p_token2_value IN VARCHAR2 := NULL,
103 p_token3 IN VARCHAR2 := NULL,
104 p_token3_value IN VARCHAR2 := NULL
105 );
106
107
108 FUNCTION Compute_Num_of_Blocks(
109 p_num_of_rows IN NUMBER,
110 p_avg_length IN NUMBER
111 )
112 RETURN NUMBER;
113
114
115 PROCEDURE Insert_Functional_Expertise(
116 p_refresh_type IN VARCHAR2,
117 p_user_id IN NUMBER
118 );
119
120 PROCEDURE Insert_Internal(
121 p_refresh_type IN VARCHAR2,
122 p_user_id IN NUMBER
123 );
124
125 PROCEDURE Insert_External(
126 p_refresh_type IN VARCHAR2,
127 p_user_id IN NUMBER
128 );
129
130 PROCEDURE Insert_Function_Perf_Attrs(
131 p_refresh_type IN VARCHAR2,
132 p_partner_id IN NUMBER
133 );
134
135 PROCEDURE Upsert_Func_Perf_Attrs (
136 p_refresh_type VARCHAR2,
137 p_partner_id NUMBER,
138 p_attribute_id NUMBER
139 );
140
141 PROCEDURE Update_Timestamp (
142 p_attribute_id IN NUMBER,
143 p_timestamp IN DATE := SYSDATE
144 );
145
146 PROCEDURE Transform_Batch_Sql (
147 p_batch_sql_text IN OUT NOCOPY VARCHAR2,
148 p_new_partner_clause IN VARCHAR2
149 );
150
151 PROCEDURE Recompile_Dependencies(
152 p_referenced_type IN VARCHAR2,
153 p_referenced_name1 IN VARCHAR2,
154 p_referenced_name2 IN VARCHAR2,
155 p_api_package_name IN VARCHAR2
156 );
157
158
159
160 --=============================================================================+
161 --| Public Procedure |
162 --| Exec_Create_Context_Val |
163 --| |
164 --| Parameters |
165 --| IN |
166 --| OUT |
167 --| |
168 --| |
169 --| NOTES |
170 --| |
171 --| HISTORY |
172 --| |
173 --==============================================================================
174 PROCEDURE Exec_Create_Context_Val ( ERRBUF OUT NOCOPY VARCHAR2,
175 RETCODE OUT NOCOPY VARCHAR2,
176 p_new_partners_only IN VARCHAR2 := 'N',
177 p_log_to_file IN VARCHAR2 := 'Y')
178
179 IS
180 -- -----------------------------------------------------------------------
181 -- Cursors
182 -- -----------------------------------------------------------------------
183 CURSOR c_underlying_tables IS
184 SELECT synonym_name, table_name
185 FROM dba_synonyms
186 WHERE owner = g_apps_schema AND
187 synonym_name IN ('PV_SEARCH_ATTR_VALUES', 'PV_SEARCH_ATTR_MIRROR');
188
189 CURSOR c_pv_schema IS
190 SELECT i.tablespace,
191 i.index_tablespace,
192 u.oracle_username
193 FROM fnd_product_installations i,
194 fnd_application a,
195 fnd_oracle_userid u
196 WHERE a.application_short_name = 'PV' AND
197 a.application_id = i.application_id AND
198 u.oracle_id = i.oracle_id;
199
200 CURSOR c_synonyms (pc_synonym_name IN VARCHAR2) IS
201 SELECT COUNT(*) count
202 FROM user_synonyms
203 WHERE synonym_name = pc_synonym_name;
204
205 CURSOR c_attribute1_refresh IS
206 SELECT COUNT(*) count
207 FROM pv_entity_attrs b,
208 pv_attributes_vl a
209 WHERE a.attribute_id = 1 AND
210 a.attribute_id = b.attribute_id AND
211 b.entity = 'PARTNER' AND
212 a.enabled_flag = 'Y' AND
213 b.enabled_flag = 'Y' AND
214 a.enable_matching_flag = 'Y' AND
215 (b.last_refresh_date IS NULL OR
216 b.refresh_frequency IS NULL OR
217 b.refresh_frequency_uom IS NULL OR
218 (b.last_refresh_date +
219 DECODE(refresh_frequency_uom,
220 'HOUR', refresh_frequency/24,
221 'DAY', refresh_frequency,
222 'WEEK', refresh_frequency * 7,
223 'MONTH', ADD_MONTHS(TRUNC(NVL(b.last_refresh_date, SYSDATE), 'MM'),
224 b.refresh_frequency)
225 - NVL(b.last_refresh_date, SYSDATE)
226 )
227 ) <= SYSDATE);
228
229
230 -- -----------------------------------------------------------------------
231 -- Local Variables
232 -- -----------------------------------------------------------------------
233 TYPE t_ref_cursor IS REF CURSOR;
234 lc_get_partners t_ref_cursor;
235
236 l_api_package_name VARCHAR2(30) := 'PV_CONTEXT_VALUES';
237 l_refresh_type VARCHAR2(30);
238 l_refresh_type_temp VARCHAR2(30);
239 l_last_incr_refresh_str VARCHAR2(100);
240 l_last_incr_refresh_date DATE;
241 l_partner_sql VARCHAR2(32000);
242 l_incr_timestamp VARCHAR2(50);
243 l_mirror_table VARCHAR2(30);
244 l_search_table VARCHAR2(30);
245 l_perf_mirror_table VARCHAR2(30);
246 l_perf_search_table VARCHAR2(30);
247 l_pv_schema_name VARCHAR2(30);
248 l_user_id NUMBER := FND_GLOBAL.USER_ID();
249 l_ret_val BOOLEAN := FALSE;
250 l_total_start NUMBER;
251 l_start NUMBER;
252 l_elapsed_time NUMBER;
253 l_partner_id NUMBER;
254 l_end_refresh_flag BOOLEAN;
255 l_elapsed_time2 NUMBER;
256
257 -- -------------------------------------------------------------------------
258 -- Makeshift variables
259 -- -------------------------------------------------------------------------
260 l_num_temp NUMBER;
261
262 BEGIN
263 -- -----------------------------------------------------------------------
264 -- Set variables.
265 -- -----------------------------------------------------------------------
266 l_total_start := dbms_utility.get_time;
267
268 IF (p_log_to_file <> 'Y') THEN
269 g_log_to_file := 'N';
270 ELSE
271 g_log_to_file := 'Y';
272 END IF;
273
274 IF (p_new_partners_only = 'Y') THEN
275 l_refresh_type := g_incr_refresh;
276 ELSE
277 l_refresh_type := g_full_refresh;
278 END IF;
279
280 g_module_name := 'Refresh Attribute Texts Concurrent Program: ' ||
281 l_refresh_type;
282
283 g_batch_insert_count := 0;
284 g_non_batch_insert_count := 0;
285
286 -- -----------------------------------------------------------------------
287 -- g_apps_schema is introduced to handle Oracle 10g schema swapping.
288 -- See bug # 3871688. Reference to schema should not be hard-coded.
289 -- -----------------------------------------------------------------------
290 IF (g_apps_schema IS NULL) THEN
291 FOR x IN (SELECT user FROM dual) LOOP
292 g_apps_schema := x.user;
293 END LOOP;
294 END IF;
295
296 -- -----------------------------------------------------------------------
297 -- Exit the program if there is already a session running.
298 -- -----------------------------------------------------------------------
299 FOR x IN (SELECT COUNT(*) count
300 FROM v$session
301 WHERE module LIKE 'Refresh Attribute Texts Concurrent Program%')
302 LOOP
303 IF (x.count > 0) THEN
304 Debug('There is already a Refresh Attribute Text CC session running.');
305 Debug('The program will now exit.');
306 RETURN;
307 END IF;
308 END LOOP;
309
310 -- -----------------------------------------------------------------------
311 -- Code Instrumentation
312 -- -----------------------------------------------------------------------
313 dbms_application_info.set_client_info(
314 client_info => 'p_new_partners_only = ' || p_new_partners_only || ' | ' ||
315 'p_log_to_file = ' || p_log_to_file
316 );
317
318 dbms_application_info.set_module(
319 module_name => g_module_name,
320 action_name => 'STARTUP'
321 );
322
323
324 -- -----------------------------------------------------------------------
325 -- Start time message...
326 -- -----------------------------------------------------------------------
327 FND_MESSAGE.SET_NAME(application => 'PV',
328 name => 'PV_CREATE_CONTEXT_START_TIME');
329 FND_MESSAGE.SET_TOKEN(token => 'P_DATE_TIME',
330 value => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') );
331
332 IF (g_log_to_file = 'Y') THEN
333 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
334 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
335
336 ELSE
337 FND_MSG_PUB.Add;
338 END IF;
339
340 -- -----------------------------------------------------------------------
341 -- Retrieve the last incremental refresh date from the profile.
342 -- An incremental refresh is done when p_new_partners_only is set to 'Y'.
343 -- -----------------------------------------------------------------------
344 IF (l_refresh_type = g_incr_refresh) THEN
345 -- --------------------------------------------------------------------
346 -- Retrieve the profile value for incremental refresh
347 -- --------------------------------------------------------------------
348 FOR x IN (SELECT profile_option_value
349 FROM fnd_profile_options a,
350 fnd_profile_option_values b
351 WHERE a.application_id = 691 AND
352 a.profile_option_id = b.profile_option_id AND
353 b.level_id = 10001 AND -- site level
354 b.level_value = 0 AND
355 a.profile_optioN_name = 'PV_REFRESH_ATTRS_LAST_UPDATE')
356 LOOP
357 l_last_incr_refresh_str := x.profile_option_value;
358 END LOOP;
359
360 -- ------------------------------------------------------------------------
361 -- Somehow FND_PROFILE.VALUE is not getting the proper value. Use the above
362 -- SQL instead.
363 -- ------------------------------------------------------------------------
364 -- l_last_incr_refresh_str := FND_PROFILE.VALUE('PV_REFRESH_ATTRS_LAST_UPDATE');
365 l_last_incr_refresh_date := NVL(TO_DATE(l_last_incr_refresh_str,
366 'MM-DD-YYYY HH24:MI:SS'),
367 TO_DATE('01-01-1900 00:00:00',
368 'MM-DD-YYYY HH24:MI:SS'));
369
370 Debug('Type of Refresh: INCREMENTAL (New Partners Only)');
371 Debug('Initiating incremental refresh...only new partners added to the ');
372 Debug('system since the last refresh date will be retrieved and updated.');
373 Debug('Last refresh date: ' || NVL(l_last_incr_refresh_str, 'Never'));
374
375 ELSE
376 Debug('Type of Refresh: FULL');
377 END IF;
378
379 -- -----------------------------------------------------------------------
380 -- Retrieve the common currency code from the profile option. This is the
381 -- currency in which all the partner's currency attribute values will be
382 -- converted to.
383 -- -----------------------------------------------------------------------
384 g_common_currency := NVL(FND_PROFILE.Value('PV_COMMON_CURRENCY'), 'USD');
385 Debug('The common currency is: ' || g_common_currency);
386
387
388 -- -----------------------------------------------------------------------
389 -- Set NLS_NUMERIC_CHARACATERS for the session to '.,' format (US).
390 -- This will ensure that numeric data that get stored in a VARCHAR2
391 -- column (e.g. currency --> '1234.55:::USD:::030705153540') are stored
392 -- in a consistent format.
393 -- This is to prevent issues discovered in bug # 4191068.
394 -- -----------------------------------------------------------------------
395 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''.,''';
396
397
398 -- -----------------------------------------------------------------------
399 -- "Freeze" the time. The next incremental refresh starts from here.
400 -- This is done for both incremental and full refresh.
401 -- -----------------------------------------------------------------------
402 l_incr_timestamp := TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS');
403
404
405 -- -----------------------------------------------------------------------
406 -- Set the flag to NOT to display message in currency conversion.
407 -- -----------------------------------------------------------------------
408 pv_check_match_pub.g_display_message := FALSE;
409 pv_check_match_pub.g_period_set_name := FND_PROFILE.Value('AS_FORECAST_CALENDAR');
410 pv_check_match_pub.g_period_type := FND_PROFILE.Value('AS_DEFAULT_PERIOD_TYPE');
411
412 g_partner_temp_table := 'PV_PARTNER_ID_SESSION';
413
414
415 -- -----------------------------------------------------------------------
416 -- Pre-processing steps including synonym recovery, retrieving PV schema,
417 -- retrieving underlying tables for the search and the mirror table,
418 -- alter/drop indexes, etc.
419 -- -----------------------------------------------------------------------
420 l_start := dbms_utility.get_time;
421 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
422 Debug('Pre-Processing....................................................');
423 dbms_application_info.set_module(
424 module_name => g_module_name,
425 action_name => 'Pre-Processing'
426 );
427
428 Pre_Processing (
429 p_refresh_type => l_refresh_type,
430 p_synonym_name => 'PV_SEARCH_ATTR_VALUES',
431 p_mirror_synonym_name => 'PV_SEARCH_ATTR_MIRROR',
432 p_temp_synonym_name => 'PV_SEARCH_ATTR_VALUES_TMP',
433 p_partner_id_temp_table => 'PV_PARTNER_ID_SESSION',
434 p_temp_table_processed => TRUE,
435 p_last_incr_refresh_str => l_last_incr_refresh_str,
436 p_log_to_file => g_log_to_file,
437 p_module_name => g_module_name,
438 p_pv_schema_name => l_pv_schema_name,
439 p_search_table => l_search_table,
440 p_mirror_table => l_mirror_table,
441 p_end_refresh_flag => l_end_refresh_flag,
442 p_out_refresh_type => l_refresh_type_temp
443 );
444
445 l_refresh_type := l_refresh_type_temp;
446
447
448 -- -----------------------------------------------------------------------
449 -- Exit the program when Pre_Processing sets p_end_refresh_flag to FALSE.
450 -- This usually happens when the refresh type is g_incr_refresh, but there are
451 -- no new partners since the last incremental refresh.
452 -- -----------------------------------------------------------------------
453 IF (l_refresh_type IN (g_incr_refresh, g_incr_full_refresh) AND l_end_refresh_flag) THEN
454 Debug('Update last refresh date...');
455
456 FND_PROFILE.PUT('PV_REFRESH_ATTRS_LAST_UPDATE', l_incr_timestamp);
457 l_ret_val := FND_PROFILE.SAVE('PV_REFRESH_ATTRS_LAST_UPDATE',
458 l_incr_timestamp,
459 'SITE');
460
461 Debug('The next incremental refresh will start from ' || l_incr_timestamp);
462
463 RETCODE := g_RETCODE;
464
465 RETURN;
466 END IF;
467
468
469 Debug('Elapsed Time (Pre-Processing): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
470 l_start := dbms_utility.get_time;
471
472
473 -- *****************************************************************
474 -- *****************************************************************
475 -- Process Attributes That Don't Need to Be Refreshed
476 -- *****************************************************************
477 -- *****************************************************************
478 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
479 Debug('Processing attributes that do not need to be refreshed................');
480 dbms_application_info.set_module(
481 module_name => g_module_name,
482 action_name => 'Processing Non-Refreshables'
483 );
484
485 IF (l_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
486 INSERT /*+ APPEND */ INTO pv_search_attr_mirror
487 (SEARCH_ATTR_VALUES_ID,
488 PARTY_ID,
489 SHORT_NAME,
490 ATTR_TEXT,
491 CREATION_DATE,
492 CREATED_BY,
493 LAST_UPDATE_DATE,
494 LAST_UPDATE_LOGIN,
495 OBJECT_VERSION_NUMBER,
496 LAST_UPDATED_BY,
497 SECURITY_GROUP_ID,
498 ATTRIBUTE_ID,
499 ATTR_VALUE
500 )
501 SELECT SEARCH_ATTR_VALUES_ID,
502 PARTY_ID,
503 SHORT_NAME,
504 ATTR_TEXT,
505 CREATION_DATE,
506 CREATED_BY,
507 LAST_UPDATE_DATE,
508 LAST_UPDATE_LOGIN,
509 OBJECT_VERSION_NUMBER,
510 LAST_UPDATED_BY,
511 SECURITY_GROUP_ID,
512 ATTRIBUTE_ID,
513 ATTR_VALUE
514 FROM pv_search_attr_values
515 WHERE attribute_id IN (
516 SELECT a.attribute_id
517 FROM pv_attributes_b a,
518 pv_entity_attrs b
519 WHERE a.attribute_id = b.attribute_id AND
520 b.entity = 'PARTNER' AND
521 a.enabled_flag = 'Y' AND
522 b.enabled_flag = 'Y' AND
523 (a.enable_matching_flag = 'Y' OR
524 b.display_external_value_flag = 'Y') AND
525 (b.last_refresh_date +
526 DECODE(refresh_frequency_uom,
527 'HOUR', refresh_frequency/24,
528 'DAY', refresh_frequency,
529 'WEEK', refresh_frequency * 7,
530 'MONTH', ADD_MONTHS(TRUNC(NVL(b.last_refresh_date, SYSDATE), 'MM'),
531 b.refresh_frequency)
532 - NVL(b.last_refresh_date, SYSDATE)
533 )
534 ) > SYSDATE);
535
536
537 Debug(SQL%ROWCOUNT || ' rows inserted.');
538
539 COMMIT;
540 END IF;
541
542 Debug('Elapsed Time (Non-Refreshables): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
543 l_start := dbms_utility.get_time;
544
545
546 -- *****************************************************************
547 -- *****************************************************************
548 -- Process Attribute ID 1 (Functional Expertise)
549 -- *****************************************************************
550 -- *****************************************************************
551 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
552 Debug('Processing Functional Expertise......................................');
553 dbms_application_info.set_module(
554 module_name => g_module_name,
555 action_name => 'Functional Attributes'
556 );
557
558 FOR x IN c_attribute1_refresh LOOP
559 l_num_temp := x.count;
560 END LOOP;
561
562 IF (l_num_temp > 0) THEN
563 Insert_Functional_Expertise(l_refresh_type, l_user_id);
564 END IF;
565
566 Debug('Elapsed Time (Functional Expertise): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
567 l_start := dbms_utility.get_time;
568
569
570 -- *****************************************************************
571 -- *****************************************************************
572 -- Process Internal Attributes
573 -- *****************************************************************
574 -- *****************************************************************
575 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
576 Debug('Processing internal attributes.............................');
577 dbms_application_info.set_module(
578 module_name => g_module_name,
579 action_name => 'Internal Attributes'
580 );
581
582 Insert_Internal(l_refresh_type, l_user_id);
583
584 Debug('Elapsed Time (Internal Attributes): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
585 l_start := dbms_utility.get_time;
586
587
588 -- *****************************************************************
589 -- *****************************************************************
590 -- Process Derived/Performance Attributes
591 -- *****************************************************************
592 -- *****************************************************************
593 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
594 Debug('-- **********************************************************');
595 Debug('-- Processing derived/performance attributes.............................');
596 Debug('-- **********************************************************');
597 dbms_application_info.set_module(
598 module_name => g_module_name,
599 action_name => 'Derived/Perf Attributes'
600 );
601
602
603 -- Reset
604 g_func_perf_attrs_tbl := g_func_perf_attrs_empty;
605
606 -- ------------------------------------------------------------------
607 -- This step will process all function attributes in either an
608 -- incremental or full refresh.
609 -- It will only process performance attributes in an incremental
610 -- refresh.
611 --
612 -- Check if there are any attributes that fall within this category.
613 -- If yes, process these attributes partner-by-partner.
614 -- ------------------------------------------------------------------
615 IF (l_refresh_type <> g_incr_refresh) THEN
616 FOR x IN (SELECT COUNT(*) count
617 FROM pv_entity_attrs a,
618 pv_attributes_vl b
619 WHERE a.attribute_id = b.attribute_id AND
620 a.entity = 'PARTNER' AND
621 a.enabled_flag = 'Y' AND
622 b.enabled_flag = 'Y' AND
623 (b.enable_matching_flag = 'Y' OR
624 a.display_external_value_flag = 'Y') AND
625 ((b.performance_flag = 'Y' AND
626 l_refresh_type = g_incr_refresh) OR
627 b.attribute_type = 'FUNCTION') AND
628 (a.last_refresh_date IS NULL OR
629 a.refresh_frequency IS NULL OR
630 a.refresh_frequency_uom IS NULL OR
631 (last_refresh_date +
632 DECODE(refresh_frequency_uom,
633 'HOUR', refresh_frequency/24,
634 'DAY', refresh_frequency,
635 'WEEK', refresh_frequency * 7,
636 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
637 refresh_frequency)
638 - NVL(last_refresh_date, SYSDATE)
639 )
640 ) <= SYSDATE) )
641 LOOP
642 l_num_temp := x.count;
643 END LOOP;
644 END IF;
645
646 -- ----------------------------------------------------------------------------------
647 -- Incremental refresh does not consider refresh frequency. Every attribute should
648 -- always be updated.
649 -- ----------------------------------------------------------------------------------
650 IF (l_num_temp > 0 OR l_refresh_type = g_incr_refresh) THEN
651 IF (l_refresh_type = g_incr_refresh AND l_last_incr_refresh_date IS NOT NULL) THEN
652 OPEN lc_get_partners FOR
653 SELECT partner_id
654 FROM pv_partner_id_session;
655
656 ELSE
657 -- -----------------------------------------------------------------------------
658 -- Obsolete the use of sales_partner_flag.
659 -- In 11.5.10, an IMP's (indirectly-managed partner) relationship with the VAD
660 -- does not have a partner resource. We don't want to include these records
661 -- in the refresh.
662 -- -----------------------------------------------------------------------------
663 OPEN lc_get_partners FOR
664 SELECT partner_id
665 FROM pv_partner_profiles
666 WHERE status = 'A' AND
667 --sales_partner_flag = 'Y'
668 partner_resource_id IS NOT NULL;
669 END IF;
670
671 LOOP
672 FETCH lc_get_partners INTO l_partner_id;
673 EXIT WHEN lc_get_partners%NOTFOUND;
674
675 Insert_Function_Perf_Attrs(l_refresh_type, l_partner_id);
676 END LOOP;
677
678 CLOSE lc_get_partners;
679
680 IF (l_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
681 COMMIT;
682 END IF;
683 END IF;
684
685 IF (l_refresh_type <> g_incr_refresh) THEN
686 FOR x IN (SELECT a.attribute_id
687 FROM pv_entity_attrs a,
688 pv_attributes_vl b
689 WHERE a.attribute_id = b.attribute_id AND
690 a.entity = 'PARTNER' AND
691 a.enabled_flag = 'Y' AND
692 b.enabled_flag = 'Y' AND
693 (b.enable_matching_flag = 'Y' OR
694 a.display_external_value_flag = 'Y') AND
695 ((b.performance_flag = 'Y' AND
696 l_refresh_type = g_incr_refresh) OR
697 b.attribute_type = 'FUNCTION') AND
698 (a.last_refresh_date IS NULL OR
699 a.refresh_frequency IS NULL OR
700 a.refresh_frequency_uom IS NULL OR
701 (last_refresh_date +
702 DECODE(refresh_frequency_uom,
703 'HOUR', refresh_frequency/24,
704 'DAY', refresh_frequency,
705 'WEEK', refresh_frequency * 7,
706 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
707 refresh_frequency)
708 - NVL(last_refresh_date, SYSDATE)
709 )
710 ) <= SYSDATE) )
711 LOOP
712 Update_Timestamp (
713 p_attribute_id => x.attribute_id,
714 p_timestamp => SYSDATE
715 );
716 END LOOP;
717 END IF;
718
719
720
721 Debug('Total Number of Rows Inserted for this operation: ' || g_non_batch_insert_count);
722 Debug('Elapsed Time (Derived/Perf Incr): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
723
724 l_start := dbms_utility.get_time;
725
726
727 -- *****************************************************************
728 -- *****************************************************************
729 -- Process External Attributes
730 -- *****************************************************************
731 -- *****************************************************************
732 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
733 Debug('Processing external attributes.........................................');
734 dbms_application_info.set_module(
735 module_name => g_module_name,
736 action_name => 'External Attributes'
737 );
738
739 Insert_External(l_refresh_type, l_user_id);
740
741 Debug('Elapsed Time (External Attributes): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
742
743
744 -- *****************************************************************
745 -- *****************************************************************
746 -- Post Loading Processing
747 -- *****************************************************************
748 -- *****************************************************************
749 l_start := dbms_utility.get_time;
750 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
751 Debug('Post loading processing...............................................');
752 dbms_application_info.set_module(
753 module_name => g_module_name,
754 action_name => 'Post Processing'
755 );
756
757 Post_Processing (
758 p_refresh_type => l_refresh_type,
759 p_synonym_name => 'PV_SEARCH_ATTR_VALUES',
760 p_mirror_synonym_name => 'PV_SEARCH_ATTR_MIRROR',
761 p_temp_synonym_name => 'PV_SEARCH_ATTR_VALUES_TMP',
762 p_pv_schema_name => l_pv_schema_name,
763 p_search_table => l_search_table,
764 p_mirror_table => l_mirror_table,
765 p_incr_timestamp => l_incr_timestamp,
766 p_module_name => g_module_name,
767 p_api_package_name => l_api_package_name,
768 p_log_to_file => g_log_to_file
769 );
770
771 -- --------------------------------------------------------------
772 -- Update last refresh date.
773 -- --------------------------------------------------------------
774 Debug('Update last refresh date...');
775 FND_PROFILE.PUT('PV_REFRESH_ATTRS_LAST_UPDATE', l_incr_timestamp);
776 l_ret_val := FND_PROFILE.SAVE('PV_REFRESH_ATTRS_LAST_UPDATE',
777 l_incr_timestamp,
778 'SITE');
779
780 Debug('The next incremental refresh will start from ' || l_incr_timestamp);
781
782 COMMIT;
783
784 Debug('Elapsed Time (Total Post-Processing): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
785
786
787
788 -- -------------------------------------------------------------------------
789 -- Display End Time Message.
790 -- -------------------------------------------------------------------------
791 Debug('=====================================================================');
792
793 FND_MESSAGE.SET_NAME(application => 'PV',
794 name => 'PV_CREATE_CONTEXT_END_TIME');
795 FND_MESSAGE.SET_TOKEN(token => 'P_DATE_TIME',
796 value => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') );
797
798 IF (g_log_to_file = 'Y') THEN
799 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
800 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
801
802 ELSE
803 FND_MSG_PUB.Add;
804 END IF;
805
806
807
808 l_elapsed_time := DBMS_UTILITY.get_time - l_total_start;
809 Debug('=====================================================================');
810 Debug('Total Elapsed Time: ' || l_elapsed_time || ' hsec' || ' = ' ||
811 ROUND((l_elapsed_time/6000), 2) || ' minutes');
812 Debug('=====================================================================');
813
814 RETCODE := g_RETCODE;
815
816 EXCEPTION
817 WHEN others THEN
818 Debug(SQLCODE || ': ' || SQLERRM);
819 RETCODE := '1';
820
821 END Exec_Create_Context_Val;
822 -- ====================End of Exec_Create_Context_Val===========================
823
824
825
826
827
828 --=============================================================================+
829 --| Public Procedure |
830 --| |
831 --| Pre_Processing |
832 --| |
833 --| Parameters |
834 --| IN |
835 --| OUT |
836 --| |
837 --| |
838 --| NOTES: |
839 --| |
840 --| HISTORY |
841 --| |
842 --==============================================================================
843 PROCEDURE Pre_Processing (
844 p_refresh_type IN VARCHAR2 := g_full_refresh,
845 p_synonym_name IN VARCHAR2,
846 p_mirror_synonym_name IN VARCHAR2,
847 p_temp_synonym_name IN VARCHAR2,
848 p_partner_id_temp_table IN VARCHAR2 := null,
849 p_temp_table_processed IN BOOLEAN := FALSE,
850 p_last_incr_refresh_str IN VARCHAR2 := null,
851 p_log_to_file IN VARCHAR2 := 'Y',
852 p_module_name IN VARCHAR2,
853 p_pv_schema_name IN OUT NOCOPY VARCHAR2,
854 p_search_table OUT NOCOPY VARCHAR2,
855 p_mirror_table OUT NOCOPY VARCHAR2,
856 p_end_refresh_flag OUT NOCOPY BOOLEAN,
857 p_out_refresh_type OUT NOCOPY VARCHAR2
858 )
859 IS
860 -- -----------------------------------------------------------------------
861 -- Cursors
862 -- -----------------------------------------------------------------------
863 CURSOR c_underlying_tables IS
864 SELECT synonym_name, table_name
865 FROM dba_synonyms
866 WHERE owner = g_apps_schema AND
867 synonym_name IN (p_synonym_name, p_mirror_synonym_name);
868
869 CURSOR c_pv_schema IS
870 SELECT i.tablespace,
871 i.index_tablespace,
872 u.oracle_username
873 FROM fnd_product_installations i,
874 fnd_application a,
875 fnd_oracle_userid u
876 WHERE a.application_short_name = 'PV' AND
877 a.application_id = i.application_id AND
878 u.oracle_id = i.oracle_id;
879
880 CURSOR c_synonyms (pc_synonym_name IN VARCHAR2) IS
881 SELECT COUNT(*) count
882 FROM user_synonyms
883 WHERE synonym_name = pc_synonym_name;
884
885 -- -----------------------------------------------------------------------
886 -- Local Variables
887 -- -----------------------------------------------------------------------
888 l_partner_sql VARCHAR2(32000);
889 l_num_of_partners NUMBER;
890 l_num_of_blocks NUMBER;
891 l_avg_length NUMBER;
892
893 BEGIN
894 p_end_refresh_flag := FALSE;
895
896 IF (p_log_to_file = 'Y') THEN
897 g_log_to_file := 'Y';
898
899 ELSE
900 g_log_to_file := 'N';
901 END IF;
902
903
904 -- -----------------------------------------------------------------------
905 -- Reset the refresh type. If the refresh type is incremental, but the
906 -- last incremental refresh timestamp is NULL, set the refresh type to
907 -- g_incr_full_refresh ('INCR-FULL'). This means that we will use full
908 -- refresh method to perform refresh, but we will still update incremental
909 -- refresh timestamp.
910 -- -----------------------------------------------------------------------
911 IF ((p_refresh_type = g_incr_refresh) AND
912 (p_last_incr_refresh_str IS NULL))
913 THEN
914 Debug('Setting the refresh type to FULL since there is no prior refresh.');
915 p_out_refresh_type := g_incr_full_refresh;
916
917 ELSE
918 p_out_refresh_type := p_refresh_type;
919 END IF;
920
921 -- -----------------------------------------------------------------------
922 -- For incremental refresh, an Oracle temporary table must exist.
923 -- If it doesn't the refresh type will be changed to a full refresh.
924 -- -----------------------------------------------------------------------
925 IF ((p_out_refresh_type = g_incr_refresh) AND (p_partner_id_temp_table IS NULL)) THEN
926 Debug('-- ************************************************************************ --');
927 Debug('-- No Oracle temporary table provided. Incremental refresh will not proceed --');
928 Debug('-- A full refresh will be performed instead. --');
929 Debug('-- ************************************************************************ --');
930 p_out_refresh_type := g_full_refresh;
931 END IF;
932
933
934 -- -----------------------------------------------------------------------
935 -- Determine "APPS" schema.
936 -- -----------------------------------------------------------------------
937 IF (g_apps_schema IS NULL) THEN
938 FOR x IN (SELECT user FROM dual) LOOP
939 g_apps_schema := x.user;
940 END LOOP;
941 END IF;
942
943
944 -- -----------------------------------------------------------------------
945 -- Determine "PV" schema.
946 -- -----------------------------------------------------------------------
947 IF (p_pv_schema_name IS NULL) THEN
948 FOR x IN c_pv_schema LOOP
949 p_pv_schema_name := x.oracle_username;
950 END LOOP;
951 END IF;
952
953 -- -----------------------------------------------------------------------
954 -- Synonym recovery: recovers the synonyms of the search and the mirror
955 -- table in the event of a system crash in the previous concurrent
956 -- program run.
957 -- -----------------------------------------------------------------------
958 Debug('Synonym Recovery..................................................');
959 FOR x IN c_synonyms(p_synonym_name) LOOP
960 IF (x.count = 0) THEN
961 Debug('RENAME ' || p_temp_synonym_name || ' TO ' || p_synonym_name);
962
963 EXECUTE IMMEDIATE
964 'RENAME ' || p_temp_synonym_name || ' TO ' || p_synonym_name;
965 END IF;
966 END LOOP;
967
968 FOR x IN c_synonyms(p_mirror_synonym_name) LOOP
969 IF (x.count = 0) THEN
970 Debug('RENAME ' || p_temp_synonym_name || ' TO ' || p_mirror_synonym_name);
971
972 EXECUTE IMMEDIATE
973 'RENAME ' || p_temp_synonym_name || ' TO ' || p_mirror_synonym_name;
974 END IF;
975 END LOOP;
976
977 -- -----------------------------------------------------------------------
978 -- Retrieve the underlying tables of the search and the mirror table.
979 -- -----------------------------------------------------------------------
980 Debug('Retrieving the underlying tables of the synonyms...................');
981 FOR x IN c_underlying_tables LOOP
982 IF (x.synonym_name = p_synonym_name) THEN
983 p_search_table := x.table_name;
984
985 ELSIF (x.synonym_name = p_mirror_synonym_name) THEN
986 p_mirror_table := x.table_name;
987 END IF;
988 END LOOP;
989
990
991 -- =======================================================================
992 -- Set the appropriate settings/parameters/attributes for different
993 -- refresh types.
994 -- =======================================================================
995 IF ((p_out_refresh_type = g_incr_refresh) AND (p_temp_table_processed)) THEN
996 -- ---------------------------------------------------------
997 -- Set partner SQL
998 -- ---------------------------------------------------------
999 l_partner_sql :=
1000 'SELECT partner_id
1001 FROM pv_partner_profiles pvpp
1002 WHERE pvpp.status = ''A'' AND
1003 partner_resource_id IS NOT NULL AND
1004 creation_date >= :last_incr_refresh ';
1005
1006 -- ---------------------------------------------------------
1007 -- Insert the list of new partners into the temporary table.
1008 -- ---------------------------------------------------------
1009 Debug('Insert the list of new partners into the temporary table.............');
1010 EXECUTE IMMEDIATE
1011 'TRUNCATE TABLE ' || p_partner_id_temp_table;
1012
1013 EXECUTE IMMEDIATE
1014 'INSERT INTO ' || p_partner_id_temp_table || ' ' ||
1015 l_partner_sql
1016 USING TO_DATE(NVL(p_last_incr_refresh_str, '12-31-1900 00:00:01'),
1017 'MM-DD-YYYY HH24:MI:SS');
1018
1019 -- ---------------------------------------------------------
1020 -- If there are no new partners, mark the flag for exiting
1021 -- the program.
1022 -- ---------------------------------------------------------
1023 IF (SQL%ROWCOUNT = 0) THEN
1024 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1025 p_msg_name => 'PV_NO_NEW_PARTNERS');
1026
1027 p_end_refresh_flag := TRUE;
1028 RETURN;
1029
1030 ELSE
1031 Debug(SQL%ROWCOUNT || ' new partners found in the system.');
1032
1033 /* -------------------------------------------------------------
1034 Debug('.');
1035 Debug('Partner ID' || ' ' || 'Partner Name');
1036 Debug('----------' || ' ' || '-------------------------------');
1037
1038 For x IN (SELECT a.partner_id, c.party_name
1039 FROM pv_partner_id_session a,
1040 pv_partner_profiles b,
1041 hz_parties c
1042 WHERE a.partner_id = b.partner_id AND
1043 b.partner_party_id = c.party_id)
1044 LOOP
1045 Debug(LPAD(TO_CHAR(x.partner_id), 12) || ' ' || x.party_name);
1046 END LOOP;
1047 Debug('.');
1048 * -------------------------------------------------------------- */
1049 END IF;
1050
1051
1052 l_num_of_partners := SQL%ROWCOUNT;
1053 l_avg_length := 7;
1054 l_num_of_blocks := Compute_Num_of_Blocks(l_num_of_partners, l_avg_length);
1055
1056 -- -------------------------------------------------------------
1057 -- Set statistics for the temporary table. Since Oracle 8i does not
1058 -- generate statistics on a temporary table even if a table is
1059 -- analyzed.
1060 -- -------------------------------------------------------------
1061 Debug('Gathering statistics on the temporary table.........................');
1062 dbms_stats.set_table_stats(ownname => USER,
1063 tabname => p_partner_id_temp_table,
1064 numrows => l_num_of_partners,
1065 numblks => l_num_of_blocks,
1066 avgrlen => l_avg_length);
1067
1068 -- =======================================================================
1069 -- Full or 'INCR-FULL' Refresh
1070 -- =======================================================================
1071 ELSE
1072 Debug('Set the mirror table to NOLOGGING mode.........................');
1073 EXECUTE IMMEDIATE
1074 'ALTER TABLE ' || p_pv_schema_name || '.' || p_mirror_table ||
1075 ' NOLOGGING';
1076
1077 -- ---------------------------------------------------------
1078 -- Truncate the mirror table whether it's empty or not.
1079 -- ---------------------------------------------------------
1080 Debug('Truncate the mirror table.......................................');
1081 EXECUTE IMMEDIATE
1082 'TRUNCATE TABLE ' || p_pv_schema_name || '.' || p_mirror_table;
1083
1084 -- ---------------------------------------------------------
1085 -- * Make all non-unique indexes unusable.
1086 -- * Disable all primary and unique constraints, which,
1087 -- in effect, drop the associated unique indexes.
1088 -- * Drop all the remaining unique indexes.
1089 -- ---------------------------------------------------------
1090 Debug('Drop unique indexes and make nonunique indexes ' ||
1091 'unusable on the mirror table...');
1092
1093 Disable_Drop_Indexes(p_mirror_table, p_pv_schema_name);
1094 END IF;
1095
1096 END Pre_Processing;
1097 -- ===========================End of Pre_Processing==========================
1098
1099
1100
1101 --=============================================================================+
1102 --| Public Procedure |
1103 --| |
1104 --| Post_Processing |
1105 --| |
1106 --| Parameters |
1107 --| IN |
1108 --| OUT |
1109 --| |
1110 --| |
1111 --| NOTES: |
1112 --| |
1113 --| HISTORY |
1114 --| |
1115 --==============================================================================
1116 PROCEDURE Post_Processing (
1117 p_refresh_type IN VARCHAR2 := g_full_refresh,
1118 p_synonym_name IN VARCHAR2,
1119 p_mirror_synonym_name IN VARCHAR2,
1120 p_temp_synonym_name IN VARCHAR2,
1121 p_pv_schema_name IN VARCHAR2,
1122 p_search_table IN VARCHAR2,
1123 p_mirror_table IN VARCHAR2,
1124 p_incr_timestamp IN VARCHAR2,
1125 p_api_package_name IN VARCHAR2,
1126 p_module_name IN VARCHAR2,
1127 p_log_to_file IN VARCHAR2 := 'Y'
1128 )
1129 IS
1130 l_start NUMBER;
1131 l_elapsed_time NUMBER;
1132 l_ret_val BOOLEAN := FALSE;
1133
1134 BEGIN
1135 IF (p_log_to_file = 'Y') THEN
1136 g_log_to_file := 'Y';
1137
1138 ELSE
1139 g_log_to_file := 'N';
1140 END IF;
1141
1142 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
1143 -- --------------------------------------------------------------
1144 -- Recreate unique indexes and rebuild nonunique indexes.
1145 -- --------------------------------------------------------------
1146 l_start := dbms_utility.get_time;
1147 dbms_application_info.set_module(
1148 module_name => p_module_name,
1149 action_name => 'Post: Rebuild Indexes'
1150 );
1151 Debug('Recreate and rebuild indexes on the mirror table AND');
1152 Debug('Synch up indexes between the search and the mirror table...');
1153
1154 Enable_Create_Indexes(
1155 p_search_table,
1156 p_mirror_table,
1157 p_pv_schema_name
1158 );
1159
1160 -- --------------------------------------------------------------
1161 -- Analyze the mirror table.
1162 -- --------------------------------------------------------------
1163 dbms_application_info.set_module(
1164 module_name => p_module_name,
1165 action_name => 'Post: Analyze Tables'
1166 );
1167 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1168 l_start := dbms_utility.get_time;
1169 Debug('Analyze the mirror table...');
1170
1171 dbms_stats.gather_table_stats(
1172 ownname => p_pv_schema_name,
1173 tabname => p_mirror_table,
1174 estimate_percent => 10,
1175 method_opt => 'FOR ALL INDEXES'
1176 );
1177
1178 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1179 l_start := dbms_utility.get_time;
1180
1181 -- --------------------------------------------------------------
1182 -- Rename synonyms.
1183 -- --------------------------------------------------------------
1184 dbms_application_info.set_module(
1185 module_name => p_module_name,
1186 action_name => 'Post: Swapping Synonyms'
1187 );
1188 Debug('Synonym swapping and other post processing activities...');
1189
1190
1191 EXECUTE IMMEDIATE
1192 'RENAME ' || p_synonym_name || ' TO ' || p_temp_synonym_name;
1193
1194 EXECUTE IMMEDIATE
1195 'RENAME ' || p_mirror_synonym_name || ' TO ' || p_synonym_name;
1196
1197 EXECUTE IMMEDIATE
1198 'RENAME ' || p_temp_synonym_name || ' TO ' || p_mirror_synonym_name;
1199
1200 -- --------------------------------------------------------------
1201 -- Recompile invalid dependent package bodies.
1202 -- --------------------------------------------------------------
1203 dbms_application_info.set_module(
1204 module_name => g_module_name,
1205 action_name => 'Post: Compile Dependencies'
1206 );
1207 Debug('Recompile invalid dependent package bodies...');
1208
1209 Recompile_Dependencies(
1210 p_referenced_type => 'SYNONYM',
1211 p_referenced_name1 => p_synonym_name,
1212 p_referenced_name2 => p_mirror_synonym_name,
1213 p_api_package_name => p_api_package_name
1214 );
1215
1216
1217 -- --------------------------------------------------------------
1218 -- Truncate the "search" table.
1219 -- --------------------------------------------------------------
1220 dbms_application_info.set_module(
1221 module_name => p_module_name,
1222 action_name => 'Post: Truncate Table'
1223 );
1224 Debug('Truncate the search table...');
1225
1226 EXECUTE IMMEDIATE
1227 'TRUNCATE TABLE ' || p_pv_schema_name || '.' || p_search_table;
1228 END IF;
1229
1230 END Post_Processing;
1231 -- ===========================End of Post_Processing==========================
1232
1233
1234
1235 -- *****************************************************************************
1236 -- *****************************************************************************
1237 -- *****************************************************************************
1238 -- *****************************************************************************
1239
1240
1241
1242
1243 --=============================================================================+
1244 --| Private Procedure |
1245 --| |
1246 --| Debug |
1247 --| |
1248 --| Parameters |
1249 --| IN |
1250 --| OUT |
1251 --| |
1252 --| |
1253 --| NOTES: |
1254 --| |
1255 --| HISTORY |
1256 --| |
1257 --==============================================================================
1258 PROCEDURE Debug(
1259 p_msg_string IN VARCHAR2,
1260 p_msg_type IN VARCHAR2 := 'PV_DEBUG_MESSAGE'
1261 )
1262 IS
1263 BEGIN
1264 FND_MESSAGE.Set_Name('PV', p_msg_type);
1265 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
1266
1267 IF (g_log_to_file = 'N') THEN
1268 FND_MSG_PUB.Add;
1269
1270 ELSIF (g_log_to_file = 'Y') THEN
1271 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
1272 END IF;
1273 END Debug;
1274 -- =================================End of Debug================================
1275
1276
1277 --=============================================================================+
1278 --| Public Procedure |
1279 --| |
1280 --| Set_Message |
1281 --| |
1282 --| Parameters |
1283 --| IN |
1284 --| OUT |
1285 --| |
1286 --| |
1287 --| NOTES: |
1288 --| |
1289 --| HISTORY |
1290 --| |
1291 --==============================================================================
1292 PROCEDURE Set_Message(
1293 p_msg_level IN NUMBER,
1294 p_msg_name IN VARCHAR2,
1295 p_token1 IN VARCHAR2 := NULL,
1296 p_token1_value IN VARCHAR2 := NULL,
1297 p_token2 IN VARCHAR2 := NULL,
1298 p_token2_value IN VARCHAR2 := NULL,
1299 p_token3 IN VARCHAR2 := NULL,
1300 p_token3_value IN VARCHAR2 := NULL
1301 )
1302 IS
1303 BEGIN
1304 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level) THEN
1305 FND_MESSAGE.Set_Name('PV', p_msg_name);
1306 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
1307
1308 IF (p_token1 IS NOT NULL) THEN
1309 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
1310 END IF;
1311
1312 IF (p_token2 IS NOT NULL) THEN
1313 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
1314 END IF;
1315
1316 IF (p_token3 IS NOT NULL) THEN
1317 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
1318 END IF;
1319
1320 IF (g_log_to_file = 'N') THEN
1321 FND_MSG_PUB.Add;
1322
1323 ELSIF (g_log_to_file = 'Y') THEN
1324 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
1325 END IF;
1326 END IF;
1327 END Set_Message;
1328 -- ==============================End of Set_Message==============================
1329
1330
1331 --=============================================================================+
1332 --| Private Function |
1333 --| |
1334 --| Compute_Num_of_Blocks |
1335 --| |
1336 --| Parameters |
1337 --| IN |
1338 --| OUT |
1339 --| |
1340 --| |
1341 --| NOTES: |
1342 --| |
1343 --| HISTORY |
1344 --| |
1345 --==============================================================================
1346 FUNCTION Compute_Num_of_Blocks(
1347 p_num_of_rows IN NUMBER,
1348 p_avg_length IN NUMBER
1349 )
1350 RETURN NUMBER
1351 IS
1352 CURSOR c_block_size IS
1353 SELECT value
1354 FROM v$parameter
1355 WHERE name = 'db_block_size';
1356
1357 l_db_block_size NUMBER;
1358 l_data_size NUMBER;
1359
1360 -- ----------------------------------------------------------------
1361 -- The part of the block that is used for block overhead.
1362 -- Set it to 1/8 --> 0.125. (this is a guestimate value).
1363 -- ----------------------------------------------------------------
1364 l_overhead_ratio NUMBER := 0.125;
1365
1366 BEGIN
1367 l_data_size := p_num_of_rows * p_avg_length;
1368
1369 FOR x IN c_block_size LOOP
1370 l_db_block_size := TO_NUMBER(x.value);
1371 END LOOP;
1372
1373 RETURN (l_db_block_size - (l_db_block_size * l_overhead_ratio))/l_data_size;
1374
1375 END Compute_Num_of_Blocks;
1376 -- ===========================End of Compute_Num_of_Blocks=======================
1377
1378
1379
1380
1381
1382
1383 --=============================================================================+
1384 --| Private Function |
1385 --| |
1386 --| Insert_Functional_Expertise |
1387 --| |
1388 --| Parameters |
1389 --| IN |
1390 --| OUT |
1391 --| |
1392 --| |
1393 --| NOTES: |
1394 --| |
1395 --| HISTORY |
1396 --| |
1397 --==============================================================================
1398 PROCEDURE Insert_Functional_Expertise (
1399 p_refresh_type IN VARCHAR2,
1400 p_user_id IN NUMBER
1401 )
1402 IS
1403
1404 l_insert_header VARCHAR2(200);
1405 l_insert_body VARCHAR2(1000);
1406 l_ddl_str VARCHAR2(4000);
1407
1408 BEGIN
1409 dbms_application_info.set_module(
1410 module_name => g_module_name,
1411 action_name => 'Inside Attribute ID 1'
1412 );
1413
1414
1415
1416 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
1417 INSERT /*+ APPEND */
1418 INTO pv_search_attr_mirror (
1419 SEARCH_ATTR_VALUES_ID,
1420 PARTY_ID,
1421 ATTRIBUTE_ID,
1422 ATTR_TEXT,
1423 CREATION_DATE,
1424 CREATED_BY,
1425 LAST_UPDATE_DATE,
1426 LAST_UPDATED_BY,
1427 LAST_UPDATE_LOGIN ,
1428 OBJECT_Version_number
1429 )
1430 SELECT pv_search_attr_values_s.nextval,
1431 entity_id,
1432 1,
1433 attr_value,
1434 SYSDATE,
1435 p_user_id,
1436 SYSDATE,
1437 p_user_id,
1438 p_user_id,
1439 1.0
1440 FROM (
1441 SELECT DISTINCT
1442 a.entity_id,
1443 DENORM.child_id attr_value
1444 FROM pv_enty_attr_values a,
1445 pv_entity_attrs b,
1446 eni_prod_denorm_hrchy_v DENORM
1447 WHERE b.attribute_id = 1 AND
1448 a.latest_flag = 'Y' AND
1449 a.entity = 'PARTNER' AND
1450 a.attr_value = TO_CHAR(DENORM.parent_id) AND
1451 a.attribute_id = b.attribute_id AND
1452 b.entity = 'PARTNER' AND
1453 (b.last_refresh_date IS NULL OR
1454 b.refresh_frequency IS NULL OR
1455 b.refresh_frequency_uom IS NULL OR
1456 (b.last_refresh_date +
1457 DECODE(b.refresh_frequency_uom,
1458 'HOUR', b.refresh_frequency/24,
1459 'DAY', b.refresh_frequency,
1460 'WEEK', b.refresh_frequency * 7,
1461 'MONTH', ADD_MONTHS(TRUNC(NVL(b.last_refresh_date, SYSDATE), 'MM'),
1462 b.refresh_frequency)
1463 - NVL(b.last_refresh_date, SYSDATE)
1464 )
1465 ) <= SYSDATE)
1466 );
1467
1468
1469 Debug(SQL%ROWCOUNT || ' rows inserted.');
1470
1471 -- ---------------------------------------------------------------
1472 -- Update timestamp
1473 -- ---------------------------------------------------------------
1474 dbms_application_info.set_module(
1475 module_name => g_module_name,
1476 action_name => 'Timestamp Attribute ID 1'
1477 );
1478
1479 Update_Timestamp (
1480 p_attribute_id => 1,
1481 p_timestamp => SYSDATE
1482 );
1483
1484 COMMIT;
1485
1486 ELSE
1490 -- inserting the records.
1487 -- ----------------------------------------------------------------
1488 -- In an incremental refresh (new partners only refresh), make
1489 -- sure the records are not already in the search table before
1491 -- ----------------------------------------------------------------
1492 DELETE FROM pv_search_attr_values
1493 WHERE attribute_id = 1 AND
1494 party_id IN (SELECT partner_id FROM pv_partner_id_session);
1495
1496
1497 INSERT
1498 INTO pv_search_attr_values (
1499 SEARCH_ATTR_VALUES_ID,
1500 PARTY_ID,
1501 ATTRIBUTE_ID,
1502 ATTR_TEXT,
1503 CREATION_DATE,
1504 CREATED_BY,
1505 LAST_UPDATE_DATE,
1506 LAST_UPDATED_BY,
1507 LAST_UPDATE_LOGIN ,
1508 OBJECT_Version_number
1509 )
1510 SELECT pv_search_attr_values_s.nextval,
1511 entity_id,
1512 1,
1513 attr_value,
1514 SYSDATE,
1515 p_user_id,
1516 SYSDATE,
1517 p_user_id,
1518 p_user_id,
1519 1.0
1520 FROM (
1521 SELECT DISTINCT
1522 a.entity_id,
1523 DENORM.child_id attr_value
1524 FROM pv_enty_attr_values a,
1525 pv_partner_id_session b,
1526 eni_prod_denorm_hrchy_v DENORM
1527 WHERE a.attribute_id = 1 AND
1528 a.latest_flag = 'Y' AND
1529 a.entity = 'PARTNER' AND
1530 a.entity_id = b.partner_id AND
1531 a.attr_value = TO_CHAR(DENORM.parent_id));
1532
1533 Debug(SQL%ROWCOUNT || ' rows inserted.');
1534
1535 END IF;
1536
1537
1538 EXCEPTION
1539 WHEN others THEN
1540 Debug('Exception raised while inserting for "functional expertise" ' ||
1541 '(Attribute ID = 1)');
1542 Debug(SQLCODE);
1543 Debug(SQLERRM);
1544
1545 g_RETCODE := '1';
1546
1547 END Insert_Functional_Expertise;
1548 -- =======================End of Insert_Functional_Expertise====================
1549
1550
1551
1552 --=============================================================================+
1553 --| Private Function |
1554 --| |
1555 --| Insert_Internal |
1556 --| |
1557 --| Parameters |
1558 --| IN |
1559 --| OUT |
1560 --| |
1561 --| |
1562 --| NOTES: |
1563 --| |
1564 --| HISTORY |
1565 --| |
1566 --==============================================================================
1567 PROCEDURE Insert_Internal (
1568 p_refresh_type IN VARCHAR2,
1569 p_user_id IN NUMBER
1570 )
1571 IS
1572 -- ----------------------------------------------------------------------
1573 -- Local Variables
1574 -- ----------------------------------------------------------------------
1575 l_last_message VARCHAR2(30000);
1576 l_start NUMBER;
1577 l_total_start NUMBER;
1578 l_count NUMBER;
1579
1580 -- ----------------------------------------------------------------------
1581 -- Currency Attributes - Internal
1582 -- ----------------------------------------------------------------------
1583 CURSOR c_currency_attrs IS
1584 SELECT a.attribute_id, b.name
1585 FROM pv_entity_attrs a,
1586 pv_attributes_vl b
1587 WHERE b.attribute_id <> 1 AND
1588 a.attribute_id = b.attribute_id AND
1589 a.enabled_flag = 'Y' AND
1590 b.enabled_flag = 'Y' AND
1591 (b.enable_matching_flag = 'Y' OR
1592 a.display_external_value_flag = 'Y') AND
1593 a.entity = 'PARTNER' AND
1594 a.attr_data_type IN ('INTERNAL', 'INT_EXT')AND
1595 (a.last_refresh_date IS NULL OR
1596 a.refresh_frequency IS NULL OR
1597 a.refresh_frequency_uom IS NULL OR
1598 (last_refresh_date +
1599 DECODE(refresh_frequency_uom,
1600 'HOUR', refresh_frequency/24,
1601 'DAY', refresh_frequency,
1602 'WEEK', refresh_frequency * 7,
1603 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
1604 refresh_frequency)
1605 - NVL(last_refresh_date, SYSDATE)
1606 )
1607 ) <= SYSDATE) AND
1608 b.return_type = 'CURRENCY'
1609 ORDER BY a.attribute_id;
1610
1611 i NUMBER := 1;
1612 l_total_rows NUMBER := 0;
1613
1614 BEGIN
1615 -- *****************************************************************
1616 -- *****************************************************************
1617 -- Currency Attributes Refresh
1618 -- *****************************************************************
1619 -- *****************************************************************
1620 l_total_start := dbms_utility.get_time;
1621
1622 Debug('-- **********************************************************');
1623 Debug('-- Processing internal CURRENCY attributes...');
1624 Debug('-- **********************************************************');
1625
1626 dbms_application_info.set_module(
1627 module_name => g_module_name,
1628 action_name => 'Internal - CURRENCY'
1629 );
1630
1631 -- -----------------------------------------------------------------
1632 -- Process currency attributes one attribute at a time.
1633 -- Within each attribute, it's a all-or-nothing operation. If
1634 -- currency conversion fails for even one record, the whole operation
1635 -- will be "rolled back".
1636 -- -----------------------------------------------------------------
1637 FOR x IN c_currency_attrs LOOP
1638 BEGIN
1639 l_start := dbms_utility.get_time;
1640
1641 -- --------------------------------------------------------------
1642 -- Full Refresh
1643 -- --------------------------------------------------------------
1644 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
1645 INSERT /*+ APPEND */
1646 INTO pv_search_attr_mirror (
1647 SEARCH_ATTR_VALUES_ID,
1648 PARTY_ID,
1649 ATTRIBUTE_ID,
1650 ATTR_TEXT,
1651 ATTR_VALUE,
1652 CREATION_DATE,
1653 CREATED_BY,
1654 LAST_UPDATE_DATE,
1655 LAST_UPDATED_BY,
1656 LAST_UPDATE_LOGIN ,
1657 OBJECT_Version_number)
1658 SELECT pv_search_attr_values_s.nextval,
1659 entity_id,
1660 x.attribute_id,
1661 attr_text,
1662 attr_value,
1663 SYSDATE,
1664 p_user_id,
1665 SYSDATE,
1666 p_user_id,
1667 p_user_id,
1668 1.0
1669 FROM (
1670 SELECT DISTINCT a.entity_id,
1671 a.attr_value attr_text,
1672 pv_check_match_pub.Currency_Conversion(
1673 a.attr_value, g_common_currency) attr_value
1674 FROM pv_enty_attr_values a,
1675 pv_partner_profiles PV
1676 WHERE a.entity = 'PARTNER' AND
1677 a.latest_flag = 'Y' AND
1678 a.entity_id = PV.partner_id AND
1679 PV.partner_resource_id IS NOT NULL AND
1680 PV.status = 'A' AND
1681 a.attr_value IS NOT NULL AND
1682 a.attribute_id = x.attribute_id);
1683
1684 Debug('Processing Attribute "' || x.name || '" (Attribute ID = ' ||
1685 x.attribute_id || ')');
1686 Debug(SQL%ROWCOUNT || ' rows processed.');
1687
1688 l_total_rows := l_total_rows + SQL%ROWCOUNT;
1689
1690
1691 Update_Timestamp (
1692 p_attribute_id => x.attribute_id,
1693 p_timestamp => SYSDATE
1694 );
1695
1696 COMMIT;
1697
1698 -- --------------------------------------------------------------
1699 -- Partial Refresh
1700 -- --------------------------------------------------------------
1701 ELSE
1702 -- ----------------------------------------------------------------
1703 -- In an incremental refresh (new partners only refresh), make
1704 -- sure the records are not already in the search table before
1705 -- inserting the records.
1706 -- ----------------------------------------------------------------
1707 DELETE FROM pv_search_attr_values
1708 WHERE attribute_id = x.attribute_id AND
1709 party_id IN (SELECT partner_id FROM pv_partner_id_session);
1710
1711
1712 INSERT
1713 INTO pv_search_attr_values (
1714 SEARCH_ATTR_VALUES_ID,
1715 PARTY_ID,
1716 ATTRIBUTE_ID,
1717 ATTR_TEXT,
1718 ATTR_VALUE,
1719 CREATION_DATE,
1720 CREATED_BY,
1721 LAST_UPDATE_DATE,
1722 LAST_UPDATED_BY,
1723 LAST_UPDATE_LOGIN ,
1724 OBJECT_Version_number)
1725 SELECT pv_search_attr_values_s.nextval,
1726 entity_id,
1727 x.attribute_id,
1728 attr_text,
1729 attr_value,
1730 SYSDATE,
1731 p_user_id,
1732 SYSDATE,
1733 p_user_id,
1734 p_user_id,
1735 1.0
1736 FROM (
1737 SELECT DISTINCT a.entity_id,
1738 a.attr_value attr_text,
1742 pv_partner_id_session b
1739 pv_check_match_pub.Currency_Conversion(
1740 a.attr_value, g_common_currency) attr_value
1741 FROM pv_enty_attr_values a,
1743 WHERE a.entity = 'PARTNER' AND
1744 a.latest_flag = 'Y' AND
1745 a.attr_value IS NOT NULL AND
1746 a.entity_id = b.partner_id AND
1747 a.attribute_id = x.attribute_id);
1748
1749 Debug('Processing Attribute "' || x.name || '" (Attribute ID = ' ||
1750 x.attribute_id || ')');
1751 Debug(SQL%ROWCOUNT || ' rows processed.');
1752
1753 l_total_rows := l_total_rows + SQL%ROWCOUNT;
1754 END IF;
1755
1756 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1757
1758 EXCEPTION
1759 WHEN FND_API.G_EXC_ERROR THEN
1760 -- -------------------------------------------------------------------
1761 -- Retrieve the last message from the message queue which
1762 -- contains the exception raised by the called program
1763 -- e.g. currency_conversion
1764 -- -------------------------------------------------------------------
1765 -- Reset the pointer to the last message of the queue
1766 fnd_msg_pub.reset(fnd_msg_pub.G_LAST);
1767
1768 -- -------------------------------------------------------------------
1769 -- Go back to the second to last message which contains the message
1770 -- raised by the called program (e.g. currency_conversion)
1771 -- -------------------------------------------------------------------
1772 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
1773 p_encoded => FND_API.g_false);
1774 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
1775 p_encoded => FND_API.g_false);
1776 Debug(l_last_message);
1777
1778 Debug('Attribute ID: ' || x.attribute_id);
1779 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
1780
1781 g_RETCODE := '1';
1782
1783 -- -------------------------------------------------------------------
1784 -- If there is an exception with curreny_conversion, we need to "roll"
1785 -- back changes. In our case, this means copy from the search table
1786 -- and insert into the mirror table.
1787 -- -------------------------------------------------------------------
1788 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
1789 INSERT /*+ APPEND */ INTO pv_search_attr_mirror
1790 (SEARCH_ATTR_VALUES_ID,
1791 PARTY_ID,
1792 SHORT_NAME,
1793 ATTR_TEXT,
1794 CREATION_DATE,
1795 CREATED_BY,
1796 LAST_UPDATE_DATE,
1797 LAST_UPDATE_LOGIN,
1798 OBJECT_VERSION_NUMBER,
1799 LAST_UPDATED_BY,
1800 SECURITY_GROUP_ID,
1801 ATTRIBUTE_ID,
1802 ATTR_VALUE
1803 )
1804 SELECT SEARCH_ATTR_VALUES_ID,
1805 PARTY_ID,
1806 SHORT_NAME,
1807 ATTR_TEXT,
1808 CREATION_DATE,
1809 CREATED_BY,
1810 LAST_UPDATE_DATE,
1811 LAST_UPDATE_LOGIN,
1812 OBJECT_VERSION_NUMBER,
1813 LAST_UPDATED_BY,
1814 SECURITY_GROUP_ID,
1815 ATTRIBUTE_ID,
1816 ATTR_VALUE
1817 FROM pv_search_attr_values
1818 WHERE attribute_id = x.attribute_id;
1819
1820 COMMIT;
1821 END IF;
1822 END;
1823 END LOOP;
1824
1825
1826
1827 -- *****************************************************************
1828 -- *****************************************************************
1829 -- Full Refresh - non-Currency Attributes
1830 -- *****************************************************************
1831 -- *****************************************************************
1832 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
1833 -- --------------------------------------------------------------------------
1834 -- Process NUMBER return_type.
1835 -- --------------------------------------------------------------------------
1836 l_start := dbms_utility.get_time;
1837 Debug('-- **********************************************************');
1838 Debug('-- Processing Internal Number attributes...');
1839 Debug('-- **********************************************************');
1840
1841 dbms_application_info.set_module(
1842 module_name => g_module_name,
1843 action_name => 'Internal - NUMBER'
1844 );
1845
1846 INSERT /*+ APPEND */
1847 INTO pv_search_attr_mirror (
1848 SEARCH_ATTR_VALUES_ID,
1849 PARTY_ID,
1850 ATTRIBUTE_ID,
1851 ATTR_VALUE,
1852 CREATION_DATE,
1853 CREATED_BY,
1857 OBJECT_Version_number)
1854 LAST_UPDATE_DATE,
1855 LAST_UPDATED_BY,
1856 LAST_UPDATE_LOGIN ,
1858 SELECT pv_search_attr_values_s.nextval,
1859 entity_id,
1860 attribute_id,
1861 TO_NUMBER(attr_value),
1862 SYSDATE,
1863 p_user_id,
1864 SYSDATE,
1865 p_user_id,
1866 p_user_id,
1867 1.0
1868 FROM (
1869 SELECT DISTINCT a.entity_id, a.attr_value attr_value, a.attribute_id
1870 FROM pv_enty_attr_values a,
1871 pv_partner_profiles PV
1872 WHERE a.entity = 'PARTNER' AND
1873 a.latest_flag = 'Y' AND
1874 a.attr_value IS NOT NULL AND
1875 a.entity_id = PV.partner_id AND
1876 PV.partner_resource_id IS NOT NULL AND
1877 PV.status = 'A' AND
1878 a.attribute_id IN (
1879 SELECT a.attribute_id
1880 FROM pv_entity_attrs a,
1881 pv_attributes_b b
1882 WHERE b.attribute_id <> 1 AND
1883 a.attribute_id = b.attribute_id AND
1884 a.enabled_flag = 'Y' AND
1885 b.enabled_flag = 'Y' AND
1886 b.enable_matching_flag = 'Y' AND
1887 a.entity = 'PARTNER' AND
1888 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
1889 (a.last_refresh_date IS NULL OR
1890 a.refresh_frequency IS NULL OR
1891 a.refresh_frequency_uom IS NULL OR
1892 (last_refresh_date +
1893 DECODE(refresh_frequency_uom,
1894 'HOUR', refresh_frequency/24,
1895 'DAY', refresh_frequency,
1896 'WEEK', refresh_frequency * 7,
1897 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
1898 refresh_frequency)
1899 - NVL(last_refresh_date, SYSDATE)
1900 )
1901 ) <= SYSDATE) AND
1902 b.return_type = 'NUMBER'));
1903
1904 Debug(SQL%ROWCOUNT || ' rows processed.');
1905 l_total_rows := l_total_rows + SQL%ROWCOUNT;
1906
1907 -- --------------------------------------------------------------------------
1908 -- Update timestamp
1909 -- --------------------------------------------------------------------------
1910 FOR x IN (SELECT a.attribute_id
1911 FROM pv_entity_attrs a,
1912 pv_attributes_b b
1913 WHERE b.attribute_id <> 1 AND
1914 a.attribute_id = b.attribute_id AND
1915 a.enabled_flag = 'Y' AND
1919 a.entity = 'PARTNER' AND
1916 b.enabled_flag = 'Y' AND
1917 (b.enable_matching_flag = 'Y' OR
1918 a.display_external_value_flag = 'Y') AND
1920 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
1921 (a.last_refresh_date IS NULL OR
1922 a.refresh_frequency IS NULL OR
1923 a.refresh_frequency_uom IS NULL OR
1924 (last_refresh_date +
1925 DECODE(refresh_frequency_uom,
1926 'HOUR', refresh_frequency/24,
1927 'DAY', refresh_frequency,
1928 'WEEK', refresh_frequency * 7,
1929 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
1930 refresh_frequency)
1931 - NVL(last_refresh_date, SYSDATE)
1932 )
1933 ) <= SYSDATE) AND
1934 b.return_type = 'NUMBER')
1935 LOOP
1936 Update_Timestamp (
1937 p_attribute_id => x.attribute_id,
1938 p_timestamp => SYSDATE
1939 );
1940 END LOOP;
1941
1942 COMMIT;
1943
1944 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1945
1946
1947 -- --------------------------------------------------------------------------
1948 -- Process return_types other than NUMBER and CURRENCY.
1949 -- --------------------------------------------------------------------------
1950 l_start := dbms_utility.get_time;
1951 Debug('-- **********************************************************');
1952 Debug('-- Processing internal OTHER attributes...');
1953 Debug('-- **********************************************************');
1954 dbms_application_info.set_module(
1955 module_name => g_module_name,
1956 action_name => 'Internal - OTHER'
1957 );
1958
1959 -- ----------------------------------------------------------------------
1960 -- In R12, there is a concept of primary partner type and secondary
1961 -- partner type (attribute_id = 3). A primary partner type is indicated
1962 -- by marking pv_enty_attr_values.attr_value_extn as 'Y'. Only primary
1963 -- partner type of a partner needs to be populated in the search table.
1964 -- ----------------------------------------------------------------------
1965 INSERT /*+ APPEND */
1966 INTO pv_search_attr_mirror (
1967 SEARCH_ATTR_VALUES_ID,
1968 PARTY_ID,
1969 ATTRIBUTE_ID,
1970 ATTR_TEXT,
1971 CREATION_DATE,
1972 CREATED_BY,
1973 LAST_UPDATE_DATE,
1974 LAST_UPDATED_BY,
1975 LAST_UPDATE_LOGIN ,
1976 OBJECT_Version_number)
1977 SELECT pv_search_attr_values_s.nextval,
1978 entity_id,
1979 attribute_id,
1980 attr_value,
1981 SYSDATE,
1982 p_user_id,
1983 SYSDATE,
1984 p_user_id,
1985 p_user_id,
1986 1.0
1987 FROM (
1988 SELECT DISTINCT a.entity_id, a.attr_value attr_value, a.attribute_id
1989 FROM pv_enty_attr_values a,
1990 pv_partner_profiles PV
1991 WHERE a.entity = 'PARTNER' AND
1992 a.latest_flag = 'Y' AND
1993 a.attr_value IS NOT NULL AND
1994 DECODE(a.attribute_id, 3, attr_value_extn, 'Y') = 'Y' AND
1995 a.entity_id = PV.partner_id AND
1996 PV.partner_resource_id IS NOT NULL AND
1997 PV.status = 'A' AND
1998 a.attribute_id IN (
1999 SELECT a.attribute_id
2000 FROM pv_entity_attrs a,
2001 pv_attributes_b b
2002 WHERE b.attribute_id <> 1 AND
2003 a.attribute_id = b.attribute_id AND
2004 a.enabled_flag = 'Y' AND
2005 b.enabled_flag = 'Y' AND
2006 (b.enable_matching_flag = 'Y' OR
2007 a.display_external_value_flag = 'Y') AND
2008 a.entity = 'PARTNER' AND
2009 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
2010 (a.last_refresh_date IS NULL OR
2011 a.refresh_frequency IS NULL OR
2012 a.refresh_frequency_uom IS NULL OR
2013 (last_refresh_date +
2014 DECODE(refresh_frequency_uom,
2015 'HOUR', refresh_frequency/24,
2016 'DAY', refresh_frequency,
2017 'WEEK', refresh_frequency * 7,
2018 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
2019 refresh_frequency)
2020 - NVL(last_refresh_date, SYSDATE)
2021 )
2022 ) <= SYSDATE) AND
2023 b.return_type NOT IN ('NUMBER', 'CURRENCY')));
2024
2025 Debug(SQL%ROWCOUNT || ' rows processed.');
2026 l_total_rows := l_total_rows + SQL%ROWCOUNT;
2027
2028 -- --------------------------------------------------------------------------
2029 -- Update timestamp
2033 pv_attributes_b b
2030 -- --------------------------------------------------------------------------
2031 FOR x IN (SELECT a.attribute_id
2032 FROM pv_entity_attrs a,
2034 WHERE b.attribute_id <> 1 AND
2035 a.attribute_id = b.attribute_id AND
2036 a.enabled_flag = 'Y' AND
2037 b.enabled_flag = 'Y' AND
2038 (b.enable_matching_flag = 'Y' OR
2039 a.display_external_value_flag = 'Y') AND
2040 a.entity = 'PARTNER' AND
2041 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
2042 (a.last_refresh_date IS NULL OR
2043 a.refresh_frequency IS NULL OR
2044 a.refresh_frequency_uom IS NULL OR
2045 (last_refresh_date +
2046 DECODE(refresh_frequency_uom,
2047 'HOUR', refresh_frequency/24,
2048 'DAY', refresh_frequency,
2049 'WEEK', refresh_frequency * 7,
2050 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
2051 refresh_frequency)
2052 - NVL(last_refresh_date, SYSDATE)
2053 )
2054 ) <= SYSDATE) AND
2055 b.return_type NOT IN ('NUMBER', 'CURRENCY'))
2056 LOOP
2057 Update_Timestamp (
2058 p_attribute_id => x.attribute_id,
2059 p_timestamp => SYSDATE
2060 );
2061 END LOOP;
2062
2063 COMMIT;
2064
2065 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
2066
2067
2068
2069 -- *****************************************************************
2070 -- *****************************************************************
2071 -- Partial Refresh - non-Currency Attributes
2072 -- *****************************************************************
2073 -- *****************************************************************
2074 ELSE
2075 -- --------------------------------------------------------------------------
2076 -- Process Internal NUMBER return_type.
2077 -- --------------------------------------------------------------------------
2078 l_start := dbms_utility.get_time;
2079 Debug('-- **********************************************************');
2080 Debug('-- Processing internal NUMBER attributes...');
2081 Debug('-- **********************************************************');
2082 dbms_application_info.set_module(
2083 module_name => g_module_name,
2084 action_name => 'Internal - NUMBER'
2085 );
2086
2087 -- ----------------------------------------------------------------
2088 -- In an incremental refresh (new partners only refresh), make
2089 -- sure the records are not already in the search table before
2090 -- inserting the records.
2091 -- ----------------------------------------------------------------
2092 DELETE FROM pv_search_attr_values
2093 WHERE party_id IN (SELECT partner_id FROM pv_partner_id_session) AND
2094 attribute_id IN (
2095 SELECT a.attribute_id
2096 FROM pv_entity_attrs a,
2097 pv_attributes_b b
2098 WHERE b.attribute_id <> 1 AND
2099 a.attribute_id = b.attribute_id AND
2100 a.enabled_flag = 'Y' AND
2101 b.enabled_flag = 'Y' AND
2102 (b.enable_matching_flag = 'Y' OR
2103 a.display_external_value_flag = 'Y') AND
2104 a.entity = 'PARTNER' AND
2105 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
2106 b.return_type = 'NUMBER');
2107
2108
2109 INSERT
2110 INTO pv_search_attr_values (
2111 SEARCH_ATTR_VALUES_ID,
2112 PARTY_ID,
2113 ATTRIBUTE_ID,
2114 ATTR_VALUE,
2115 CREATION_DATE,
2116 CREATED_BY,
2117 LAST_UPDATE_DATE,
2118 LAST_UPDATED_BY,
2119 LAST_UPDATE_LOGIN ,
2120 OBJECT_Version_number)
2121 SELECT pv_search_attr_values_s.nextval,
2122 entity_id,
2123 attribute_id,
2124 TO_NUMBER(attr_value),
2125 SYSDATE,
2126 p_user_id,
2127 SYSDATE,
2128 p_user_id,
2129 p_user_id,
2130 1.0
2131 FROM (
2132 SELECT DISTINCT a.entity_id, attr_value attr_value, attribute_id
2133 FROM pv_enty_attr_values a,
2134 pv_partner_id_session b
2135 WHERE a.entity = 'PARTNER' AND
2136 a.entity_id = b.partner_id AND
2137 latest_flag = 'Y' AND
2138 attr_value IS NOT NULL AND
2139 attribute_id IN (
2140 SELECT a.attribute_id
2141 FROM pv_entity_attrs a,
2142 pv_attributes_b b
2143 WHERE b.attribute_id <> 1 AND
2144 a.attribute_id = b.attribute_id AND
2145 a.enabled_flag = 'Y' AND
2149 a.entity = 'PARTNER' AND
2146 b.enabled_flag = 'Y' AND
2147 (b.enable_matching_flag = 'Y' OR
2148 a.display_external_value_flag = 'Y') AND
2150 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
2151 b.return_type = 'NUMBER'));
2152
2153 Debug(SQL%ROWCOUNT || ' rows processed.');
2154 l_total_rows := l_total_rows + SQL%ROWCOUNT;
2155
2156 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
2157
2158
2159 -- --------------------------------------------------------------------------
2160 -- Process return_types other than NUMBER and CURRENCY.
2161 -- --------------------------------------------------------------------------
2162 l_start := dbms_utility.get_time;
2163 Debug('-- **********************************************************');
2164 Debug('-- Processing internal OTHER attributes...');
2165 Debug('-- **********************************************************');
2166 dbms_application_info.set_module(
2167 module_name => g_module_name,
2168 action_name => 'Internal - OTHER'
2169 );
2170
2171 -- ----------------------------------------------------------------
2172 -- In an incremental refresh (new partners only refresh), make
2173 -- sure the records are not already in the search table before
2174 -- inserting the records.
2175 -- ----------------------------------------------------------------
2176 DELETE FROM pv_search_attr_values
2177 WHERE party_id IN (SELECT partner_id FROM pv_partner_id_session) AND
2178 attribute_id IN (
2179 SELECT a.attribute_id
2180 FROM pv_entity_attrs a,
2181 pv_attributes_b b
2182 WHERE b.attribute_id <> 1 AND
2183 a.attribute_id = b.attribute_id AND
2184 a.enabled_flag = 'Y' AND
2185 b.enabled_flag = 'Y' AND
2186 (b.enable_matching_flag = 'Y' OR
2187 a.display_external_value_flag = 'Y') AND
2188 a.entity = 'PARTNER' AND
2189 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
2190 b.return_type NOT IN ('NUMBER', 'CURRENCY'));
2191
2192 -- ----------------------------------------------------------------------
2193 -- In R12, there is a concept of primary partner type and secondary
2194 -- partner type (attribute_id = 3). A primary partner type is indicated
2195 -- by marking pv_enty_attr_values.attr_value_extn as 'Y'. Only primary
2196 -- partner type of a partner needs to be populated in the search table.
2197 -- ----------------------------------------------------------------------
2198
2199 INSERT
2200 INTO pv_search_attr_values (
2201 SEARCH_ATTR_VALUES_ID,
2202 PARTY_ID,
2203 ATTRIBUTE_ID,
2204 ATTR_TEXT,
2205 CREATION_DATE,
2206 CREATED_BY,
2207 LAST_UPDATE_DATE,
2208 LAST_UPDATED_BY,
2209 LAST_UPDATE_LOGIN ,
2210 OBJECT_Version_number)
2211 SELECT pv_search_attr_values_s.nextval,
2212 entity_id,
2213 attribute_id,
2214 attr_value,
2215 SYSDATE,
2216 p_user_id,
2217 SYSDATE,
2218 p_user_id,
2219 p_user_id,
2220 1.0
2221 FROM (
2222 SELECT DISTINCT a.entity_id, attr_value attr_value, attribute_id
2223 FROM pv_enty_attr_values a,
2224 pv_partner_id_session b
2225 WHERE a.entity = 'PARTNER' AND
2226 a.entity_id = b.partner_id AND
2227 latest_flag = 'Y' AND
2228 attr_value IS NOT NULL AND
2229 DECODE(a.attribute_id, 3, attr_value_extn, 'Y') = 'Y' AND
2230 attribute_id IN (
2231 SELECT a.attribute_id
2232 FROM pv_entity_attrs a,
2233 pv_attributes_b b
2234 WHERE b.attribute_id <> 1 AND
2235 a.attribute_id = b.attribute_id AND
2236 a.enabled_flag = 'Y' AND
2237 b.enabled_flag = 'Y' AND
2238 (b.enable_matching_flag = 'Y' OR
2239 a.display_external_value_flag = 'Y') AND
2240 a.entity = 'PARTNER' AND
2241 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
2242 b.return_type NOT IN ('NUMBER', 'CURRENCY')));
2243
2244 Debug(SQL%ROWCOUNT || ' rows processed.');
2245 l_total_rows := l_total_rows + SQL%ROWCOUNT;
2246
2247 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
2248 END IF;
2249
2250
2251 Debug('Total Elapsed Time (Internal: ' ||
2252 (DBMS_UTILITY.get_time - l_total_start) || ' hsec');
2253 Debug('Total Number of Rows Processed for This Operation: ' || l_total_rows);
2254 Debug('Throughput: ' ||
2255 ROUND((l_total_rows/(DBMS_UTILITY.get_time - l_total_start)) * 100, 2) ||
2256 ' rows/second');
2257
2258 EXCEPTION
2259 WHEN others THEN
2260 Debug('Exception Raised...');
2261 Debug(SQLCODE);
2265 END Insert_Internal;
2262 Debug(SQLERRM);
2263 g_RETCODE := '1';
2264
2266 -- ======================End of Insert_Internal =====================================
2267
2268
2269 --=============================================================================+
2270 --| Private Function |
2271 --| |
2272 --| Insert_External |
2273 --| |
2274 --| Parameters |
2275 --| IN |
2276 --| OUT |
2277 --| |
2278 --| |
2279 --| NOTES: |
2280 --| |
2281 --| HISTORY |
2282 --| |
2283 --==============================================================================
2284 PROCEDURE Insert_External (
2285 p_refresh_type IN VARCHAR2,
2286 p_user_id IN NUMBER
2287 )
2288 IS
2289 CURSOR c_num_cur_attributes IS
2290 SELECT a.attribute_id, a.batch_sql_text, b.name, b.return_type,
2291 b.performance_flag
2292 FROM pv_entity_attrs a,
2293 pv_attributes_vl b
2294 WHERE b.attribute_id <> 1 AND
2295 a.attribute_id = b.attribute_id AND
2296 a.enabled_flag = 'Y' AND
2297 b.enabled_flag = 'Y' AND
2298 (b.enable_matching_flag = 'Y' OR
2299 a.display_external_value_flag = 'Y') AND
2300 a.entity = 'PARTNER' AND
2301 (a.attr_data_type IN ('EXTERNAL', 'EXT_INT') OR
2302 (NVL(b.performance_flag, 'N') = 'Y')) AND
2303 b.attribute_type <> 'FUNCTION' AND
2304 b.return_type IN ('NUMBER', 'CURRENCY')
2305 ORDER BY a.attribute_id;
2306
2307
2308 CURSOR c_other_attributes IS
2309 SELECT a.attribute_id, a.batch_sql_text, b.name, b.return_type,
2310 b.performance_flag
2311 FROM pv_entity_attrs a,
2312 pv_attributes_vl b
2313 WHERE b.attribute_id <> 1 AND
2314 a.attribute_id = b.attribute_id AND
2315 a.enabled_flag = 'Y' AND
2316 b.enabled_flag = 'Y' AND
2317 (b.enable_matching_flag = 'Y' OR
2318 a.display_external_value_flag = 'Y') AND
2319 a.entity = 'PARTNER' AND
2320 (a.attr_data_type IN ('EXTERNAL', 'EXT_INT') OR
2321 NVL(b.performance_flag, 'N') = 'Y') AND
2322 b.attribute_type <> 'FUNCTION' AND
2323 b.return_type NOT IN ('NUMBER', 'CURRENCY')
2324 ORDER BY a.attribute_id;
2325
2326
2327 TYPE t_ref_cursor IS REF CURSOR;
2328 lc_currency_att_values t_ref_cursor;
2329
2330 l_do_not_process BOOLEAN := FALSE;
2331
2332 l_start NUMBER;
2333 l_start2 NUMBER;
2334 l_insert_sql VARCHAR2(4000);
2335 l_ddl_sql VARCHAR2(32000);
2336 l_batch_sql_text VARCHAR2(5000);
2337 l_partner_id NUMBER;
2338 l_last_message VARCHAR2(30000);
2339 l_new_partner_clause VARCHAR2(100) :=
2340 ' AND partner_id IN (SELECT partner_id FROM ' || g_partner_temp_table || ')';
2341
2342 BEGIN
2343 -- *****************************************************************
2344 -- *****************************************************************
2345 -- Full/Incr Refresh - External Attributes
2346 -- *****************************************************************
2347 -- *****************************************************************
2348 -- --------------------------------------------------------------------------
2349 -- Set the insert statement, which will later be appended with batch_sql_text
2350 -- to create the full insert statment.
2351 -- --------------------------------------------------------------------------
2352 l_insert_sql :=
2353 'INSERT /*+ APPEND */
2354 INTO pv_search_attr_mirror (
2355 SEARCH_ATTR_VALUES_ID,
2356 PARTY_ID,
2357 ATTRIBUTE_ID,
2358 ATTR_TEXT_DUMMY,
2359 CREATION_DATE,
2360 CREATED_BY,
2361 LAST_UPDATE_DATE,
2362 LAST_UPDATED_BY,
2363 LAST_UPDATE_LOGIN ,
2364 OBJECT_Version_number)
2365 SELECT pv_search_attr_values_s.nextval,
2366 partner_id,
2367 ATTRIBUTE_ID_DUMMY,
2368 --attr_value,
2369 SYSDATE,
2370 :p_user_id,
2371 SYSDATE,
2372 :p_user_id,
2373 :p_user_id,
2374 1.0
2375 FROM (';
2376
2377
2378 IF (p_refresh_type = g_incr_refresh) THEN
2379 l_insert_sql := REPLACE(l_insert_sql, '/*+ APPEND */', ' ');
2383
2380 l_insert_sql := REPLACE(l_insert_sql, 'pv_search_attr_mirror (',
2381 'pv_search_attr_values (');
2382 END IF;
2384 -- --------------------------------------------------------------------------
2385 -- Process NUMBER and CURRENCY return_type.
2386 -- --------------------------------------------------------------------------
2387 l_start := dbms_utility.get_time;
2388 Debug('-- **********************************************************');
2389 Debug('-- Processing External NUMBER and CURRENCY attributes...');
2390 Debug('-- **********************************************************');
2391 dbms_application_info.set_module(
2392 module_name => g_module_name,
2393 action_name => 'External - NUM/CURRENCY'
2394 );
2395
2396
2397 FOR x IN c_num_cur_attributes LOOP
2398 l_do_not_process := FALSE;
2399 -- -----------------------------------------------------------------
2400 -- Determine the attribute should be refreshed based on refresh
2401 -- frequency.
2402 -- -----------------------------------------------------------------
2403 IF (p_refresh_type <> g_incr_refresh) THEN
2404 FOR y IN (SELECT COUNT(*) cnt
2405 FROM pv_entity_attrs
2406 WHERE attribute_id = x.attribute_id AND
2407 entity = 'PARTNER' AND
2408 (last_refresh_date IS NULL OR
2409 refresh_frequency IS NULL OR
2410 refresh_frequency_uom IS NULL OR
2411 refresh_frequency_uom IS NULL OR
2412 (last_refresh_date +
2413 DECODE(refresh_frequency_uom,
2414 'HOUR', refresh_frequency/24,
2415 'DAY', refresh_frequency,
2416 'WEEK', refresh_frequency * 7,
2417 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
2418 refresh_frequency)
2419 - NVL(last_refresh_date, SYSDATE)
2420 )
2421 ) <= SYSDATE))
2422 LOOP
2423 IF (y.cnt = 0) THEN
2424 l_do_not_process := TRUE;
2425 END IF;
2426 END LOOP;
2427 END IF;
2428
2429 IF (NOT l_do_not_process) THEN
2430
2431 IF (p_refresh_type = g_incr_refresh AND x.performance_flag = 'Y') THEN
2432 -- ---------------------------------------------------------------
2433 -- Cannot use batch_sql_text to perform refresh for performance
2434 -- attrubutes in an incremental refresh.
2435 -- ---------------------------------------------------------------
2436 null;
2437
2438 ELSE
2439 l_start2 := dbms_utility.get_time;
2440
2441 IF (x.batch_sql_text IS NULL) THEN
2442 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2443 p_msg_name => 'PV_ABSENT_BATCH_SQL_TEXT',
2444 p_token1 => 'Attribute Name',
2445 p_token1_value => x.name,
2446 p_token2 => 'Attribute ID',
2447 p_token2_value => x.attribute_id);
2448
2449 ELSE
2450
2451
2452 -- ----------------------------------------------------------------
2453 -- Replace ATTRIBUTE_ID_DUMMY with the real attribute_id. Also
2454 -- append the batch_sql_text to the insert statement.
2455 -- ----------------------------------------------------------------
2456 IF (x.return_type = 'CURRENCY') THEN
2457 -- -----------------------------------------------------------------
2458 -- For performance attributes, we want to change ATTRIBUTE_ID_DUMMY
2459 -- into:
2460 -- <attribute_id>,
2461 -- attr_value attr_text,
2462 -- SUBSTR(attr_value, 1, INSTR(attr_value, '':::'') - 1) ATTR_VALUE
2463 --
2464 -- That is, we want to parse out the currency amount in the currency
2465 -- string and insert the value into attr_value column in
2466 -- pv_search_attr_values.
2467 -- -----------------------------------------------------------------
2468 IF (x.performance_flag = 'Y') THEN
2469 l_ddl_sql := REPLACE(l_insert_sql, 'partner_id', 'entity_id partner_id');
2470
2471 l_ddl_sql := REPLACE(l_ddl_sql, 'ATTRIBUTE_ID_DUMMY',
2472 x.attribute_id || ', attr_value ATTR_TEXT, ' ||
2473 'SUBSTR(attr_value, 1, INSTR(attr_value, '':::'') - 1) ATTR_VALUE');
2474
2475 -- -----------------------------------------------------------------
2476 -- For non-performance attributes, we want to change
2477 -- ATTRIBUTE_ID_DUMMY into:
2478 --
2479 -- <attribute_id>,
2480 -- attr_value attr_text,
2481 -- pv_check_match_pub.currency_conversion(
2482 -- attr_value, <g_common_currency>, 'Y') ATTR_VALUE
2483 --
2484 -- -----------------------------------------------------------------
2485 ELSE
2489 'attr_value, ''' || g_common_currency || ''', ''Y'') ATTR_VALUE');
2486 l_ddl_sql := REPLACE(l_insert_sql, 'ATTRIBUTE_ID_DUMMY',
2487 x.attribute_id || ', attr_value ATTR_TEXT, ' ||
2488 'pv_check_match_pub.currency_conversion(' ||
2490
2491 END IF;
2492
2493
2494 l_ddl_sql := REPLACE(l_ddl_sql,
2495 'ATTR_TEXT_DUMMY',
2496 'ATTR_TEXT, ATTR_VALUE');
2497
2498 -- ----------------------------------------------------------------
2499 -- Non-Currency Attributes
2500 -- ----------------------------------------------------------------
2501 ELSE
2502 IF (x.performance_flag = 'Y') THEN
2503 l_ddl_sql := REPLACE(l_insert_sql, 'partner_id', 'entity_id partner_id');
2504
2505 ELSE
2506 l_ddl_sql := l_insert_sql;
2507 END IF;
2508
2509 l_ddl_sql := REPLACE(l_ddl_sql, 'ATTRIBUTE_ID_DUMMY',
2510 x.attribute_id || ', attr_value');
2511
2512 l_ddl_sql := REPLACE(l_ddl_sql, 'ATTR_TEXT_DUMMY', 'ATTR_VALUE');
2513 END IF;
2514
2515
2516 l_batch_sql_text := x.batch_sql_text;
2517
2518
2519 IF (p_refresh_type = g_incr_refresh) THEN
2520 -- ----------------------------------------------------------------
2521 -- In an incremental refresh (new partners only refresh), make
2522 -- sure the records are not already in the search table before
2523 -- inserting the records.
2524 -- ----------------------------------------------------------------
2525 DELETE FROM pv_search_attr_values
2526 WHERE attribute_id = x.attribute_id AND
2527 party_id IN (SELECT partner_id FROM pv_partner_id_session);
2528
2529 -- -------------------------------------------------------------
2530 -- Include the new partners only clause in the batch_sql_text.
2531 -- -------------------------------------------------------------
2532 Transform_Batch_Sql(l_batch_sql_text, l_new_partner_clause);
2533 END IF;
2534
2535
2536 l_ddl_sql := l_ddl_sql || l_batch_sql_text || ')';
2537
2538 -- ----------------------------------------------------------------
2539 -- Execute the insert.
2540 -- ----------------------------------------------------------------
2541 BEGIN
2542 Debug('Processing Attribute "' || x.name || '" (Attribute ID = ' ||
2543 x.attribute_id || ')');
2544
2545 IF (x.return_type = 'CURRENCY' AND x.performance_flag = 'Y') THEN
2546 EXECUTE IMMEDIATE l_ddl_sql
2547 USING p_user_id, p_user_id, p_user_id,
2548 g_common_currency, pv_check_match_pub.g_period_set_name,
2549 x.attribute_id, 'PARTNER';
2550
2551 ELSE
2552 EXECUTE IMMEDIATE l_ddl_sql USING p_user_id, p_user_id, p_user_id,
2553 x.attribute_id, 'PARTNER';
2554 END IF;
2555
2556
2557 Debug(SQL%ROWCOUNT || ' rows processed.');
2558
2559 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
2560 Update_Timestamp (
2561 p_attribute_id => x.attribute_id,
2562 p_timestamp => SYSDATE
2563 );
2564
2565 COMMIT;
2566 END IF;
2567
2568 Debug('Elapsed Time: ' ||
2569 (DBMS_UTILITY.get_time - l_start2) || ' hsec');
2570
2571
2572 EXCEPTION
2573 WHEN FND_API.G_EXC_ERROR THEN
2574 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2575 -- -------------------------------------------------------------------
2576 -- Retrieve the last message from the message queue which
2577 -- contains the exception raised by the called program
2578 -- e.g. currency_conversion
2579 -- -------------------------------------------------------------------
2580 -- Reset the pointer to the last message of the queue
2581 fnd_msg_pub.reset(fnd_msg_pub.G_LAST);
2582
2583 -- -------------------------------------------------------------------
2584 -- Go back to the second to last message which contains the message
2585 -- raised by the called program (e.g. currency_conversion)
2586 -- -------------------------------------------------------------------
2587 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
2588 p_encoded => FND_API.g_false);
2589 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
2590 p_encoded => FND_API.g_false);
2591 Debug(l_last_message);
2592
2593 Debug('----------------------------------------------------');
2597 g_RETCODE := '1';
2594 Debug('Attribute ID: ' || x.attribute_id);
2595 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2596
2598
2599
2600 -- -------------------------------------------------------------------
2601 -- If there is an exception with curreny_conversion, we need to "roll"
2602 -- back changes. In our case, this means copy from the search table
2603 -- and insert into the mirror table.
2604 -- -------------------------------------------------------------------
2605 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
2606 INSERT /*+ APPEND */ INTO pv_search_attr_mirror
2607 (SEARCH_ATTR_VALUES_ID,
2608 PARTY_ID,
2609 SHORT_NAME,
2610 ATTR_TEXT,
2611 CREATION_DATE,
2612 CREATED_BY,
2613 LAST_UPDATE_DATE,
2614 LAST_UPDATE_LOGIN,
2615 OBJECT_VERSION_NUMBER,
2616 LAST_UPDATED_BY,
2617 SECURITY_GROUP_ID,
2618 ATTRIBUTE_ID,
2619 ATTR_VALUE
2620 )
2621 SELECT SEARCH_ATTR_VALUES_ID,
2622 PARTY_ID,
2623 SHORT_NAME,
2624 ATTR_TEXT,
2625 CREATION_DATE,
2626 CREATED_BY,
2627 LAST_UPDATE_DATE,
2628 LAST_UPDATE_LOGIN,
2629 OBJECT_VERSION_NUMBER,
2630 LAST_UPDATED_BY,
2631 SECURITY_GROUP_ID,
2632 ATTRIBUTE_ID,
2633 ATTR_VALUE
2634 FROM pv_search_attr_values
2635 WHERE attribute_id = x.attribute_id;
2636
2637 COMMIT;
2638 END IF;
2639
2640 WHEN others THEN
2641 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2642 Debug('Error executing insert statement for "' || x.name || '"');
2643 Debug('Attribute ID: ' || x.attribute_id);
2644 Debug(SQLCODE || '==>' || SQLERRM);
2645 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2646 g_RETCODE := '1';
2647
2648 -- -------------------------------------------------------------------
2649 -- If there is an exception with curreny_conversion, we need to "roll"
2650 -- back changes. In our case, this means copy from the search table
2651 -- and insert into the mirror table.
2652 -- -------------------------------------------------------------------
2653 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
2654 INSERT /*+ APPEND */ INTO pv_search_attr_mirror
2655 (SEARCH_ATTR_VALUES_ID,
2656 PARTY_ID,
2657 SHORT_NAME,
2658 ATTR_TEXT,
2659 CREATION_DATE,
2660 CREATED_BY,
2661 LAST_UPDATE_DATE,
2662 LAST_UPDATE_LOGIN,
2663 OBJECT_VERSION_NUMBER,
2664 LAST_UPDATED_BY,
2665 SECURITY_GROUP_ID,
2666 ATTRIBUTE_ID,
2667 ATTR_VALUE
2668 )
2669 SELECT SEARCH_ATTR_VALUES_ID,
2670 PARTY_ID,
2671 SHORT_NAME,
2672 ATTR_TEXT,
2673 CREATION_DATE,
2674 CREATED_BY,
2675 LAST_UPDATE_DATE,
2676 LAST_UPDATE_LOGIN,
2677 OBJECT_VERSION_NUMBER,
2678 LAST_UPDATED_BY,
2679 SECURITY_GROUP_ID,
2680 ATTRIBUTE_ID,
2681 ATTR_VALUE
2682 FROM pv_search_attr_values
2683 WHERE attribute_id = x.attribute_id;
2684
2685 COMMIT;
2686 END IF;
2687 END;
2688 END IF;
2689 END IF;
2690 END IF;
2691 END LOOP;
2692
2693
2694 Debug('Elapsed Time (NUMBER/CURRENCY): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
2695
2696 -- --------------------------------------------------------------------------
2697 -- Process return_types other than NUMBER and CURRENCY.
2698 -- --------------------------------------------------------------------------
2699 l_start := dbms_utility.get_time;
2700 Debug('___________________________________________________________');
2701 Debug('-- **********************************************************');
2702 Debug('-- Processing External OTHER attributes...');
2703 Debug('-- **********************************************************');
2704
2708 );
2705 dbms_application_info.set_module(
2706 module_name => g_module_name,
2707 action_name => 'External - OTHER'
2709
2710
2711 FOR x IN c_other_attributes LOOP
2712 l_do_not_process := FALSE;
2713 -- -----------------------------------------------------------------
2714 -- Determine the attribute should be refreshed based on refresh
2715 -- frequency.
2716 -- -----------------------------------------------------------------
2717 IF (p_refresh_type <> g_incr_refresh) THEN
2718 FOR y IN (SELECT COUNT(*) cnt
2719 FROM pv_entity_attrs
2720 WHERE attribute_id = x.attribute_id AND
2721 entity = 'PARTNER' AND
2722 (last_refresh_date IS NULL OR
2723 refresh_frequency IS NULL OR
2724 refresh_frequency_uom IS NULL OR
2725 (last_refresh_date +
2726 DECODE(refresh_frequency_uom,
2727 'HOUR', refresh_frequency/24,
2728 'DAY', refresh_frequency,
2729 'WEEK', refresh_frequency * 7,
2730 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
2731 refresh_frequency)
2732 - NVL(last_refresh_date, SYSDATE)
2733 )
2734 ) <= SYSDATE))
2735 LOOP
2736 IF (y.cnt = 0) THEN
2737 l_do_not_process := TRUE;
2738 END IF;
2739 END LOOP;
2740 END IF;
2741
2742 IF (NOT l_do_not_process) THEN
2743 IF (p_refresh_type = g_incr_refresh AND x.performance_flag = 'Y') THEN
2744 -- ---------------------------------------------------------------
2745 -- Cannot use batch_sql_text to perform refresh for performance
2746 -- attrubutes in an incremental refresh.
2747 -- ---------------------------------------------------------------
2748 null;
2749
2750 ELSE
2751
2752 l_start2 := dbms_utility.get_time;
2753
2754 IF (x.batch_sql_text IS NULL) THEN
2755 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2756 p_msg_name => 'PV_ABSENT_BATCH_SQL_TEXT',
2757 p_token1 => 'Attribute Name',
2758 p_token1_value => x.name,
2759 p_token2 => 'Attribute ID',
2760 p_token2_value => x.attribute_id);
2761
2762 ELSE
2763 -- ----------------------------------------------------------------
2764 -- Replace ATTRIBUTE_ID_DUMMY with the real attribute_id. Also
2765 -- append the batch_sql_text to the insert statement.
2766 -- ----------------------------------------------------------------
2767 l_ddl_sql := REPLACE(l_insert_sql, 'ATTRIBUTE_ID_DUMMY',
2768 x.attribute_id || ', ATTR_VALUE');
2769
2770 l_ddl_sql := REPLACE(l_ddl_sql, 'ATTR_TEXT_DUMMY', 'ATTR_TEXT');
2771
2772 l_batch_sql_text := x.batch_sql_text;
2773
2774 IF (p_refresh_type = g_incr_refresh) THEN
2775 -- ----------------------------------------------------------------
2776 -- In an incremental refresh (new partners only refresh), make
2777 -- sure the records are not already in the search table before
2778 -- inserting the records.
2779 -- ----------------------------------------------------------------
2780 DELETE FROM pv_search_attr_values
2781 WHERE attribute_id = x.attribute_id AND
2782 party_id IN (SELECT partner_id FROM pv_partner_id_session);
2783
2784 -- -------------------------------------------------------------
2785 -- Include the new partners only clause in the batch_sql_text.
2786 -- -------------------------------------------------------------
2787 Transform_Batch_Sql(l_batch_sql_text, l_new_partner_clause);
2788 END IF;
2789
2790
2791 l_ddl_sql := l_ddl_sql || l_batch_sql_text || ')';
2792
2793 -- ----------------------------------------------------------------
2794 -- Execute the insert.
2795 -- ----------------------------------------------------------------
2796 BEGIN
2797 Debug('Processing Attribute "' || x.name || '" (Attribute ID = ' ||
2798 x.attribute_id || ')');
2799
2800 EXECUTE IMMEDIATE l_ddl_sql USING p_user_id, p_user_id, p_user_id,
2801 x.attribute_id, 'PARTNER';
2802
2803 Debug(SQL%ROWCOUNT || ' rows processed.');
2804
2805 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
2806 Update_Timestamp (
2807 p_attribute_id => x.attribute_id,
2808 p_timestamp => SYSDATE
2809 );
2810
2811 COMMIT;
2812 END IF;
2813
2814 Debug('Elapsed Time: ' ||
2815 (DBMS_UTILITY.get_time - l_start2) || ' hsec');
2816
2817 EXCEPTION
2821 Debug(SQLCODE || '==>' || SQLERRM);
2818 WHEN others THEN
2819 Debug('Error executing insert statement for "' || x.name || '"');
2820 Debug('Attribute ID: ' || x.attribute_id);
2822 g_RETCODE := '1';
2823 END;
2824 END IF;
2825 END IF;
2826 END IF;
2827 END LOOP;
2828
2829
2830 Debug('Elapsed Time (Other): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
2831
2832 END Insert_External;
2833 -- ======================End of Insert_External==================================
2834
2835
2836
2837 --=============================================================================+
2838 --| Private Function |
2839 --| |
2840 --| Insert_Function_Perf_Attrs |
2841 --| |
2842 --| Parameters |
2843 --| IN |
2844 --| OUT |
2845 --| |
2846 --| |
2847 --| NOTES: |
2848 --| |
2849 --| HISTORY |
2850 --| |
2851 --==============================================================================
2852 PROCEDURE Insert_Function_Perf_Attrs(
2853 p_refresh_type IN VARCHAR2,
2854 p_partner_id IN NUMBER
2855 )
2856 IS
2857 -- -----------------------------------------------------------------------
2858 -- Template cursor defined here so we can use %ROWTYPE for x.
2859 -- -----------------------------------------------------------------------
2860 CURSOR lc_template IS
2861 SELECT b.attribute_id, b.performance_flag, b.attribute_type,
2862 a.sql_text, b.name, b.return_type
2863 FROM pv_entity_attrs a,
2864 pv_attributes_vl b
2865 WHERE a.attribute_id = b.attribute_id;
2866
2867 x lc_template%ROWTYPE;
2868
2869 TYPE t_ref_cursor IS REF CURSOR;
2870 c_func_perf_attrs t_ref_cursor;
2871
2872 i NUMBER;
2873 l_start NUMBER;
2874
2875 BEGIN
2876 -- ------------------------------------------------------------------------
2877 -- The cursor for getting function and performance attributes will only
2878 -- be open once. At that time, they are fetched to a table of record.
2879 -- If there are any problem with the sql_text, the exception is caught in
2880 -- the exception block and the table of record will not have that
2881 -- particular attribute ID. This ensures that the error message associated
2882 -- with a particular sql_text will only be displayed once.
2883 --
2884 -- Note this cursor is used for function attributes (full and incremental
2885 -- refresh) and performance attributes (incremental refresh only).
2886 -- ------------------------------------------------------------------------
2887 IF (g_func_perf_attrs_tbl.COUNT = 0) THEN
2888 -- ----------------------------------------------------------------------
2889 -- Incremental refresh does not consider refresh frequency.
2890 -- ----------------------------------------------------------------------
2891 IF (p_refresh_type = g_incr_refresh) THEN
2892 OPEN c_func_perf_attrs FOR
2893 SELECT b.attribute_id, b.performance_flag, b.attribute_type,
2894 a.sql_text, b.name, b.return_type
2895 FROM pv_entity_attrs a,
2896 pv_attributes_vl b
2897 WHERE a.attribute_id = b.attribute_id AND
2898 a.entity = 'PARTNER' AND
2899 a.enabled_flag = 'Y' AND
2900 b.enabled_flag = 'Y' AND
2901 (b.enable_matching_flag = 'Y' OR
2902 a.display_external_value_flag = 'Y') AND
2903 ((b.performance_flag = 'Y' AND
2904 p_refresh_type = g_incr_refresh) OR
2905 b.attribute_type = 'FUNCTION')
2906 ORDER BY b.attribute_id;
2907
2908 -- ----------------------------------------------------------------------
2909 -- Full refresh needs to account for refresh frequency.
2910 -- ----------------------------------------------------------------------
2911 ELSE
2912 OPEN c_func_perf_attrs FOR
2913 SELECT b.attribute_id, b.performance_flag, b.attribute_type,
2914 a.sql_text, b.name, b.return_type
2915 FROM pv_entity_attrs a,
2916 pv_attributes_vl b
2917 WHERE a.attribute_id = b.attribute_id AND
2918 a.entity = 'PARTNER' AND
2919 a.enabled_flag = 'Y' AND
2920 b.enabled_flag = 'Y' AND
2921 (b.enable_matching_flag = 'Y' OR
2922 a.display_external_value_flag = 'Y') AND
2923 ((b.performance_flag = 'Y' AND
2924 p_refresh_type = g_incr_refresh) OR
2925 b.attribute_type = 'FUNCTION') AND
2926 (a.last_refresh_date IS NULL OR
2927 a.refresh_frequency IS NULL OR
2928 a.refresh_frequency_uom IS NULL OR
2929 (last_refresh_date +
2930 DECODE(refresh_frequency_uom,
2931 'HOUR', refresh_frequency/24,
2932 'DAY', refresh_frequency,
2933 'WEEK', refresh_frequency * 7,
2934 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
2935 refresh_frequency)
2936 - NVL(last_refresh_date, SYSDATE)
2937 )
2938 ) <= SYSDATE)
2939 ORDER BY b.attribute_id;
2940 END IF;
2941
2942 LOOP
2943 FETCH c_func_perf_attrs INTO x;
2944 EXIT WHEN c_func_perf_attrs%NOTFOUND;
2945
2946 BEGIN
2947 IF (x.sql_text IS NULL OR LENGTH(x.sql_text) = 0) THEN
2948 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2949 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2950 p_msg_name => 'PV_ABSENT_SQL_TEXT',
2951 p_token1 => 'Attribute Name',
2952 p_token1_value => x.name,
2953 p_token2 => 'Attribute ID',
2954 p_token2_value => x.attribute_id);
2955 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2956
2957 g_RETCODE := '1';
2958
2959 ELSE
2960 -- -----------------------------------------------------------------
2961 -- Calling UPSERT.
2962 -- -----------------------------------------------------------------
2963 g_func_perf_attrs_tbl(x.attribute_id).performance_flag := x.performance_flag;
2964 g_func_perf_attrs_tbl(x.attribute_id).attribute_type := x.attribute_type;
2965 g_func_perf_attrs_tbl(x.attribute_id).return_type := x.return_type;
2966 g_func_perf_attrs_tbl(x.attribute_id).sql_text := x.sql_text;
2967
2968 Debug('Processing attribute (' || x.name || ') (Attribute ID=' ||
2969 x.attribute_id || ')');
2970 UPSERT_func_perf_attrs(p_refresh_type, p_partner_id, x.attribute_id);
2971
2972 END IF;
2973
2974 EXCEPTION
2975 WHEN g_e_invalid_sql THEN
2976 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2977 Debug(SQLCODE);
2978 Debug(SQLERRM);
2979 Debug('Attribute Name: ' || x.name);
2980 Debug('Attribute ID : ' || x.attribute_id);
2981 Debug('The SQL Text for this attribute is invalid.');
2982 Debug('sql_text = ' || x.sql_text);
2983 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2984 g_RETCODE := '1';
2985
2986 -- --------------------------------------------------------------
2987 -- Don't process this attribute again.
2988 -- --------------------------------------------------------------
2989 g_func_perf_attrs_tbl.DELETE(x.attribute_id);
2990
2991 WHEN g_e_undeclared_identifier THEN
2992 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2993 Debug(SQLCODE);
2994 Debug(SQLERRM);
2998 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2995 Debug('Attribute Name: ' || x.name);
2996 Debug('Attribute ID : ' || x.attribute_id);
2997 Debug('sql_text = ' || x.sql_text);
2999 g_RETCODE := '1';
3000
3001 -- --------------------------------------------------------------
3002 -- Don't process this attribute again.
3003 -- --------------------------------------------------------------
3004 g_func_perf_attrs_tbl.DELETE(x.attribute_id);
3005
3006 WHEN g_e_invliad_column_name THEN
3007 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3008 Debug(SQLCODE);
3009 Debug(SQLERRM);
3010 Debug('Attribute Name: ' || x.name);
3011 Debug('Attribute ID : ' || x.attribute_id);
3012 Debug('The SQL has an invalid column name.');
3013 Debug('sql_text = ' || x.sql_text);
3014 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3015 g_RETCODE := '1';
3016
3017 -- --------------------------------------------------------------
3018 -- Don't process this attribute again.
3019 -- --------------------------------------------------------------
3020 g_func_perf_attrs_tbl.DELETE(x.attribute_id);
3021
3022 WHEN g_e_divisor_is_zero THEN
3023 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3024 Debug(SQLCODE);
3025 Debug(SQLERRM);
3026 Debug('Attribute Name: ' || x.name);
3027 Debug('Attribute ID : ' || x.attribute_id);
3028 Debug('sql_text = ' || x.sql_text);
3029 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3030 g_RETCODE := '1';
3031
3032 -- --------------------------------------------------------------
3033 -- Don't process this attribute again.
3034 -- --------------------------------------------------------------
3035 g_func_perf_attrs_tbl.DELETE(x.attribute_id);
3036
3037 WHEN others THEN
3038 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3039 Debug(SQLCODE);
3040 Debug(SQLERRM);
3041 Debug('Attribute Name: ' || x.name);
3042 Debug('Attribute ID : ' || x.attribute_id);
3043 Debug('There is an error with this SQL text.');
3044 Debug('sql_text = ' || x.sql_text);
3045 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3046 g_RETCODE := '1';
3047
3048 -- --------------------------------------------------------------
3049 -- Don't process this attribute again.
3050 -- --------------------------------------------------------------
3051 g_func_perf_attrs_tbl.DELETE(x.attribute_id);
3052 END;
3053 END LOOP;
3054
3055 -- ------------------------------------------------------------------------
3056 -- g_func_perf_attrs PLSQ table already been populated.
3057 -- ------------------------------------------------------------------------
3058 ELSE
3059 i := g_func_perf_attrs_tbl.FIRST;
3060
3061 WHILE (i <= g_func_perf_attrs_tbl.LAST) LOOP
3062 BEGIN
3063 -- -----------------------------------------------------------------
3064 -- Calling UPSERT.
3065 -- -----------------------------------------------------------------
3066 UPSERT_func_perf_attrs(p_refresh_type, p_partner_id, i);
3067
3068 i := g_func_perf_attrs_tbl.NEXT(i);
3069
3070 EXCEPTION
3071 WHEN g_e_divisor_is_zero THEN
3072 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3073 Debug(SQLCODE);
3074 Debug(SQLERRM);
3075 Debug('Attribute ID : ' || i);
3076 Debug('sql_text = ' || g_func_perf_attrs_tbl(i).sql_text);
3077 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3078 g_RETCODE := '1';
3079
3080 -- --------------------------------------------------------------
3081 -- Don't process this attribute again.
3082 -- --------------------------------------------------------------
3083 g_func_perf_attrs_tbl.DELETE(i);
3084
3085 -- --------------------------------------------------------------
3086 -- It's extremely important to advance the counter (i) here.
3087 -- Without doing this, this becomes an infinite loop!
3088 -- --------------------------------------------------------------
3089 i := g_func_perf_attrs_tbl.NEXT(i);
3090
3091 WHEN others THEN
3092 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3093 Debug(SQLCODE);
3094 Debug(SQLERRM);
3095 Debug('Attribute ID : ' || i);
3096 Debug('There is an error with this SQL text.');
3097 Debug('sql_text = ' || g_func_perf_attrs_tbl(i).sql_text);
3098 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3099 g_RETCODE := '1';
3100
3101 -- --------------------------------------------------------------
3102 -- Don't process this attribute again.
3103 -- --------------------------------------------------------------
3104 g_func_perf_attrs_tbl.DELETE(i);
3105
3106 i := g_func_perf_attrs_tbl.NEXT(i);
3107
3108 END;
3109 END LOOP;
3110 END IF;
3111
3112 END Insert_Function_Perf_Attrs;
3113 -- =======================End of Insert_Function_Perf_Attrs====================
3114
3115
3116
3117 --=============================================================================+
3118 --| Private Function |
3119 --| |
3120 --| Upsert_Func_Perf_Attrs |
3121 --| |
3122 --| Parameters |
3123 --| IN |
3124 --| OUT |
3125 --| |
3126 --| |
3127 --| NOTES: |
3128 --| |
3129 --| HISTORY |
3130 --| |
3131 --==============================================================================
3132 PROCEDURE Upsert_Func_Perf_Attrs (
3133 p_refresh_type VARCHAR2,
3134 p_partner_id NUMBER,
3135 p_attribute_id NUMBER
3136 )
3137 IS
3138 TYPE t_ref_cursor IS REF CURSOR;
3139 c_perf_attributes t_ref_cursor;
3140
3141 l_output_tbl JTF_VARCHAR2_TABLE_4000;
3142 l_tmp_tbl JTF_VARCHAR2_TABLE_4000;
3143 l_user_id NUMBER := FND_GLOBAL.USER_ID();
3144 l_output VARCHAR2(2000);
3145 l_attr_text VARCHAR2(2000);
3146 l_attr_value NUMBER;
3147 l_last_message VARCHAR2(30000);
3148
3149 BEGIN
3150 -- ------------------------------------------------------------------------
3151 -- Function Attributes
3152 -- ------------------------------------------------------------------------
3153 IF (g_func_perf_attrs_tbl(p_attribute_id).attribute_type = 'FUNCTION') THEN
3154
3155 -- ---------------------------------------------------------------------
3156 -- Execute sql_text to retrieve attribute values.
3157 -- ---------------------------------------------------------------------
3158 EXECUTE IMMEDIATE 'BEGIN ' ||
3159 g_func_perf_attrs_tbl(p_attribute_id).sql_text ||
3160 '; END;'
3161 USING p_partner_id, OUT l_output_tbl;
3162
3163 -- ---------------------------------------------------------------------
3164 -- De-dupe l_output_tbl by "selecting" its distinct values into another
3165 -- PLSQL table.
3166 -- ---------------------------------------------------------------------
3167 SELECT CAST(MULTISET(
3168 SELECT DISTINCT column_value
3169 FROM TABLE (CAST(l_output_tbl AS JTF_VARCHAR2_TABLE_4000)))
3170 AS JTF_VARCHAR2_TABLE_4000)
3171 INTO l_tmp_tbl
3172 FROM dual;
3173
3174 l_output_tbl := l_tmp_tbl;
3175
3176
3177 -- ---------------------------------------------------------------------
3178 -- Insert records retrieved from executing the function in the sql_text.
3179 -- ---------------------------------------------------------------------
3180 FOR i IN 1..l_output_tbl.COUNT LOOP
3181 BEGIN
3182 -- ------------------------------------------------------------------
3183 -- Make sure that if the currency string is NULL (note:
3184 -- ':::USD:::20031113094020' is considered as NULL since there is no
3185 -- amount), set both l_attr_text and l_attr_value to NULL so that the
3186 -- value won't be inserted into the search table.
3187 -- ------------------------------------------------------------------
3188 IF (g_func_perf_attrs_tbl(p_attribute_id).return_type = 'CURRENCY') THEN
3189 IF (l_output_tbl(i) IS NULL OR
3190 (SUBSTR(l_output_tbl(i), 1, INSTR(l_output_tbl(i), ':::') - 1)) IS NULL)
3191 THEN
3192 l_attr_text := NULL;
3193 l_attr_value := NULL;
3194
3195 ELSE
3196 l_attr_text := l_output_tbl(i);
3197 l_attr_value := pv_check_match_pub.currency_conversion
3198 (l_output_tbl(i), g_common_currency);
3199 END IF;
3200
3201 ELSIF (g_func_perf_attrs_tbl(p_attribute_id).return_type = 'NUMBER') THEN
3202 l_attr_text := NULL;
3203 l_attr_value := TO_NUMBER(l_output_tbl(i));
3204
3205 ELSE
3206 l_attr_text := l_output_tbl(i);
3207 l_attr_value := NULL;
3208 END IF;
3209
3210 -- ------------------------------------------------------------------
3211 -- Incremental Refresh
3212 -- ------------------------------------------------------------------
3213 IF (p_refresh_type = g_incr_refresh) THEN
3214 -- ----------------------------------------------------------------
3215 -- In an incremental refresh (new partners only refresh), make
3216 -- sure the records are not already in the search table before
3217 -- inserting the records.
3218 -- ----------------------------------------------------------------
3219 DELETE FROM pv_search_attr_values
3220 WHERE attribute_id = p_attribute_id AND
3221 party_id = p_partner_id;
3222
3223
3224 IF (l_attr_text IS NOT NULL OR l_attr_value IS NOT NULL) THEN
3225 INSERT INTO pv_search_attr_values (
3226 SEARCH_ATTR_VALUES_ID,
3227 PARTY_ID,
3228 ATTRIBUTE_ID,
3229 ATTR_TEXT,
3230 ATTR_VALUE,
3231 CREATION_DATE,
3232 CREATED_BY,
3233 LAST_UPDATE_DATE,
3234 LAST_UPDATED_BY,
3235 LAST_UPDATE_LOGIN ,
3236 OBJECT_Version_number)
3237 VALUES (
3238 pv_search_attr_values_s.nextval,
3239 p_partner_id,
3240 p_attribute_id,
3241 l_attr_text,
3242 l_attr_value,
3243 SYSDATE,
3244 l_user_id,
3245 SYSDATE,
3246 l_user_id,
3247 l_user_id,
3248 1.0
3249 );
3250
3251 g_non_batch_insert_count := g_non_batch_insert_count + 1;
3252 END IF;
3253
3254 -- ------------------------------------------------------------------
3255 -- Full Refresh
3256 -- ------------------------------------------------------------------
3257 ELSE
3258 IF (l_attr_text IS NOT NULL OR l_attr_value IS NOT NULL) THEN
3259 INSERT INTO pv_search_attr_mirror (
3260 SEARCH_ATTR_VALUES_ID,
3261 PARTY_ID,
3262 ATTRIBUTE_ID,
3263 ATTR_TEXT,
3264 ATTR_VALUE,
3265 CREATION_DATE,
3266 CREATED_BY,
3267 LAST_UPDATE_DATE,
3268 LAST_UPDATED_BY,
3269 LAST_UPDATE_LOGIN ,
3270 OBJECT_Version_number)
3271 VALUES (
3272 pv_search_attr_values_s.nextval,
3273 p_partner_id,
3274 p_attribute_id,
3275 l_attr_text,
3276 l_attr_value,
3277 SYSDATE,
3278 l_user_id,
3279 SYSDATE,
3280 l_user_id,
3281 l_user_id,
3282 1.0
3283 );
3284
3285 g_non_batch_insert_count := g_non_batch_insert_count + 1;
3286 END IF;
3287 END If;
3288
3289 EXCEPTION
3290 WHEN FND_API.G_EXC_ERROR THEN
3291 -- -------------------------------------------------------------------
3292 -- Retrieve the last message from the message queue which
3293 -- contains the exception raised by the called program
3294 -- e.g. currency_conversion
3295 -- -------------------------------------------------------------------
3296 -- Reset the pointer to the last message of the queue
3297 fnd_msg_pub.reset(fnd_msg_pub.G_LAST);
3298
3299 -- -------------------------------------------------------------------
3300 -- Go back to the second to last message which contains the message
3301 -- raised by the called program (e.g. currency_conversion)
3302 -- -------------------------------------------------------------------
3303 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
3304 p_encoded => FND_API.g_false);
3305 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
3306 p_encoded => FND_API.g_false);
3307 Debug(l_last_message);
3308
3312
3309 Debug('Attribute ID: ' || p_attribute_id);
3310 Debug('Partner ID: ' || p_partner_id);
3311 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
3313 g_RETCODE := '1';
3314
3315 END;
3316 END LOOP;
3317
3318 -- ------------------------------------------------------------------------
3319 -- Performance Attributes
3320 -- ------------------------------------------------------------------------
3321 ELSE
3322 OPEN c_perf_attributes FOR g_func_perf_attrs_tbl(p_attribute_id).sql_text
3323 USING p_attribute_id, 'PARTNER', p_partner_id;
3324
3325
3326 LOOP
3327 FETCH c_perf_attributes INTO l_output;
3328 EXIT WHEN c_perf_attributes%NOTFOUND;
3329
3330 BEGIN
3331
3332 IF (g_func_perf_attrs_tbl(p_attribute_id).return_type = 'CURRENCY') THEN
3333 IF (l_output IS NULL OR
3334 (SUBSTR(l_output, 1, INSTR(l_output, ':::') - 1)) IS NULL)
3335 THEN
3336 l_attr_text := NULL;
3337 l_attr_value := NULL;
3338
3339 ELSE
3340 l_attr_text := l_output;
3341 l_attr_value := pv_check_match_pub.currency_conversion
3342 (l_output, g_common_currency);
3343 END IF;
3344
3345 ELSIF (g_func_perf_attrs_tbl(p_attribute_id).return_type = 'NUMBER') THEN
3346 l_attr_text := NULL;
3347 l_attr_value := TO_NUMBER(l_output);
3348
3349 ELSE
3350 l_attr_text := l_output;
3351 l_attr_value := NULL;
3352 END IF;
3353
3354 -- ------------------------------------------------------------------
3355 -- Incremental Refresh
3356 -- ------------------------------------------------------------------
3357 IF (p_refresh_type = g_incr_refresh) THEN
3358 -- ----------------------------------------------------------------
3359 -- In an incremental refresh (new partners only refresh), make
3360 -- sure the records are not already in the search table before
3361 -- inserting the records.
3362 -- ----------------------------------------------------------------
3363 DELETE FROM pv_search_attr_values
3364 WHERE attribute_id = p_attribute_id AND
3365 party_id = p_partner_id;
3366
3367 IF (l_attr_text IS NOT NULL OR l_attr_value IS NOT NULL) THEN
3368 INSERT INTO pv_search_attr_values (
3369 SEARCH_ATTR_VALUES_ID,
3370 PARTY_ID,
3371 ATTRIBUTE_ID,
3372 ATTR_TEXT,
3373 ATTR_VALUE,
3374 CREATION_DATE,
3375 CREATED_BY,
3376 LAST_UPDATE_DATE,
3377 LAST_UPDATED_BY,
3378 LAST_UPDATE_LOGIN ,
3379 OBJECT_Version_number)
3380 VALUES (
3381 pv_search_attr_values_s.nextval,
3382 p_partner_id,
3383 p_attribute_id,
3384 l_attr_text,
3385 l_attr_value,
3386 SYSDATE,
3387 l_user_id,
3388 SYSDATE,
3389 l_user_id,
3390 l_user_id,
3391 1.0
3392 );
3393
3394 g_non_batch_insert_count := g_non_batch_insert_count + 1;
3395 END IF;
3396
3397 -- ------------------------------------------------------------------
3398 -- Full Refresh
3399 -- ------------------------------------------------------------------
3400 ELSE
3401 Debug('Wrong Entry: the codes should never have enter this area.');
3402 Debug('Full Refresh of performance attributes does not use sql_text.');
3403 END If;
3404
3405 EXCEPTION
3406 WHEN FND_API.G_EXC_ERROR THEN
3407 -- -------------------------------------------------------------------
3408 -- Retrieve the last message from the message queue which
3409 -- contains the exception raised by the called program
3410 -- e.g. currency_conversion
3411 -- -------------------------------------------------------------------
3412 -- Reset the pointer to the last message of the queue
3413 fnd_msg_pub.reset(fnd_msg_pub.G_LAST);
3414
3415 -- -------------------------------------------------------------------
3416 -- Go back to the second to last message which contains the message
3417 -- raised by the called program (e.g. currency_conversion)
3418 -- -------------------------------------------------------------------
3419 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
3420 p_encoded => FND_API.g_false);
3421 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
3422 p_encoded => FND_API.g_false);
3423 Debug(l_last_message);
3424
3425 Debug('Attribute ID: ' || p_attribute_id);
3426 Debug('Partner ID: ' || p_partner_id);
3427 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
3428
3429 g_RETCODE := '1';
3430
3431 END;
3432 END LOOP;
3433
3434 CLOSE c_perf_attributes;
3435 END IF;
3436
3437 END Upsert_Func_Perf_Attrs;
3438 -- =======================End of Upsert_Func_Perf_Attrs========================
3439
3440
3441
3442
3443
3444 --=============================================================================+
3448 --| |
3445 --| Private Function |
3446 --| |
3447 --| Transform_Batch_Sql |
3449 --| Parameters |
3450 --| IN |
3451 --| OUT |
3452 --| |
3453 --| |
3454 --| NOTES: This procedure is only used in the case of a INCR refresh. |
3455 --| |
3456 --| HISTORY |
3457 --| |
3458 --==============================================================================
3459 PROCEDURE Transform_Batch_Sql (
3460 p_batch_sql_text IN OUT NOCOPY VARCHAR2,
3461 p_new_partner_clause IN VARCHAR2
3462 )
3463 IS
3464 l_group_str VARCHAR2(20) := 'GROUP ';
3465 l_by_str VARCHAR2(10) := 'BY';
3466 l_group_by VARCHAR2(25);
3467
3468 BEGIN
3469 FOR i IN 1..10 LOOP
3470 l_group_by := l_group_str || l_by_str;
3471
3472 IF (INSTR(UPPER(p_batch_sql_text), l_group_by) > 0) THEN
3473 p_batch_sql_text :=
3474 REPLACE(UPPER(p_batch_sql_text), l_group_by,
3475 p_new_partner_clause || ' ' || l_group_by);
3476
3477 RETURN;
3478 END IF;
3479
3480 l_group_str := l_group_str || ' ';
3481 END LOOP;
3482
3483 p_batch_sql_text := p_batch_sql_text || ' ' || p_new_partner_clause;
3484
3485 END Transform_Batch_Sql;
3486 -- ======================End of Transform_Batch_Sql=============================
3487
3488
3489
3490
3491 --=============================================================================+
3492 --| Private Function |
3493 --| |
3494 --| Update_Timestamp |
3495 --| |
3496 --| Parameters |
3497 --| IN |
3498 --| OUT |
3499 --| |
3500 --| |
3501 --| NOTES: This procedure is only used in the case of a FULL refresh. |
3502 --| |
3503 --| HISTORY |
3504 --| |
3505 --==============================================================================
3506 PROCEDURE Update_Timestamp (
3507 p_attribute_id NUMBER,
3508 p_timestamp DATE := SYSDATE
3509 )
3510 IS
3511
3512 BEGIN
3513 UPDATE pv_entity_attrs
3514 SET last_refresh_date = p_timestamp
3515 WHERE entity = 'PARTNER' AND
3516 attribute_id = p_attribute_id;
3517
3518 END Update_Timestamp;
3519 -- ======================End of Update_Timestamp================================
3520
3521
3522
3523
3524 --=============================================================================+
3525 --| Private Function |
3526 --| |
3527 --| Disable_Drop_Indexes |
3528 --| |
3529 --| Parameters |
3530 --| IN |
3531 --| OUT |
3532 --| |
3533 --| |
3534 --| NOTES: |
3535 --| |
3536 --| HISTORY |
3537 --| |
3538 --==============================================================================
3539 PROCEDURE Disable_Drop_Indexes(
3540 p_mirror_table IN VARCHAR2,
3541 p_pv_schema_owner IN VARCHAR2
3542 )
3543 IS
3544 CURSOR c_indexes (pc_mirror_table IN VARCHAR2,
3545 pc_pv_schema_owner IN VARCHAR2,
3546 pc_index_type IN VARCHAR2)
3547 IS
3548 SELECT a.index_name
3549 FROM dba_indexes a
3550 WHERE a.table_name = pc_mirror_table AND
3551 a.table_owner = pc_pv_schema_owner AND
3552 a.uniqueness = pc_index_type AND
3553 a.owner = pc_pv_schema_owner
3554 ORDER BY a.index_name;
3555
3556 CURSOR c_pk_unique_constraints (pc_mirror_table IN VARCHAR2,
3557 pc_pv_schema_owner IN VARCHAR2)
3558 IS
3559 SELECT constraint_name
3560 FROM dba_constraints
3561 WHERE table_name = pc_mirror_table AND
3562 owner = pc_pv_schema_owner AND
3563 constraint_type IN ('P', 'U');
3564
3565 BEGIN
3566 -- ---------------------------------------------------------------------------
3567 -- Make all non-unique indexes on the mirror table unusable.
3568 -- ---------------------------------------------------------------------------
3569 FOR x IN c_indexes(p_mirror_table, p_pv_schema_owner, 'NONUNIQUE') LOOP
3570 EXECUTE IMMEDIATE
3571 'ALTER INDEX ' || p_pv_schema_owner || '.' || x.index_name || ' UNUSABLE';
3572 END LOOP;
3573
3574 -- ---------------------------------------------------------------------------
3575 -- Set SKIP_UNUSABLE_INDEXES session variable.
3576 -- ---------------------------------------------------------------------------
3577 EXECUTE IMMEDIATE
3578 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE';
3579
3580 -- ---------------------------------------------------------------------------
3581 -- On the mirror table:
3582 -- Disable all primary and unique constraints, which, in effect, drop the
3583 -- associated unique indexes.
3584 -- ---------------------------------------------------------------------------
3585 FOR x IN c_pk_unique_constraints(p_mirror_table, p_pv_schema_owner) LOOP
3586 EXECUTE IMMEDIATE
3587 'ALTER TABLE ' || p_pv_schema_owner || '.' || p_mirror_table ||
3588 ' MODIFY CONSTRAINT ' || x.constraint_name || ' DISABLE';
3589 END LOOP;
3590
3591 -- ---------------------------------------------------------------------------
3592 -- On the mirror table:
3593 -- Drop all the remaining unique indexes.
3594 -- ---------------------------------------------------------------------------
3595 FOR x IN c_indexes(p_mirror_table, p_pv_schema_owner, 'UNIQUE') LOOP
3596 EXECUTE IMMEDIATE
3597 'DROP INDEX ' || p_pv_schema_owner || '.' || x.index_name;
3598 END LOOP;
3599
3600 END Disable_Drop_Indexes;
3601 -- ======================End of Disable_Drop_Indexes============================
3602
3603
3604 --=============================================================================+
3605 --| Private Function |
3606 --| |
3607 --| Enable_Create_Indexes |
3608 --| |
3609 --| Parameters |
3610 --| IN |
3611 --| OUT |
3612 --| |
3613 --| |
3614 --| NOTES: |
3615 --| |
3616 --| HISTORY |
3617 --| |
3618 --==============================================================================
3619 PROCEDURE Enable_Create_Indexes(
3620 p_search_table IN VARCHAR2,
3621 p_mirror_table IN VARCHAR2,
3622 p_pv_schema_owner IN VARCHAR2
3623 ) IS
3624 -- ---------------------------------------------------------------------------
3625 -- Retrieve indexes for a table.
3626 -- ---------------------------------------------------------------------------
3630 SELECT a.index_name, a.owner, a.tablespace_name, a.pct_free,
3627 CURSOR c_indexes (pc_table_name IN VARCHAR2,
3628 pc_pv_schema_owner IN VARCHAR2)
3629 IS
3631 a.uniqueness
3632 FROM dba_indexes a
3633 WHERE a.table_name = pc_table_name AND
3634 a.table_owner = pc_pv_schema_owner AND
3635 a.uniqueness = 'NONUNIQUE' AND
3636 a.owner = pc_pv_schema_owner
3637 ORDER BY a.index_name;
3638
3639 -- ---------------------------------------------------------------------------
3640 -- Retrieve primary/unique constraints for a table.
3641 -- ---------------------------------------------------------------------------
3642 CURSOR c_pk_unique_constraints (pc_mirror_table IN VARCHAR2,
3643 pc_pv_schema_owner IN VARCHAR2)
3644 IS
3645 SELECT constraint_name
3646 FROM dba_constraints
3647 WHERE table_name = pc_mirror_table AND
3648 owner = pc_pv_schema_owner AND
3649 constraint_type IN ('P', 'U');
3650
3651
3652 -- ---------------------------------------------------------------------------
3653 -- Local Variables.
3654 -- ---------------------------------------------------------------------------
3655 l_index_ddl_stmt VARCHAR2(4000);
3656
3657 BEGIN
3658 -- ------------------------------------------------------------
3659 -- Rebuild nonunique indexes.
3660 -- ------------------------------------------------------------
3661 FOR x IN c_indexes(p_mirror_table, p_pv_schema_owner) LOOP
3662 l_index_ddl_stmt := 'ALTER INDEX ' || x.owner || '.' ||
3663 x.index_name || ' REBUILD NOLOGGING';
3664
3665 EXECUTE IMMEDIATE l_index_ddl_stmt;
3666 END LOOP;
3667
3668 -- ------------------------------------------------------------
3669 -- Set SKIP_UNUSABLE_INDEXES back to FALSE.
3670 -- ------------------------------------------------------------
3671 EXECUTE IMMEDIATE
3672 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = FALSE';
3673
3674 -- ------------------------------------------------------------------
3675 -- Recreate indexes. |
3676 -- ------------------
3677 -- Note that we only need to create unique indexes for they get
3678 -- dropped in the beginning of this program. However, there may be
3679 -- indexes added to the search table since the last refresh. These
3680 -- new indexes must also be present in the mirror table. To resolve
3681 -- this problem, we will recreate all the indexes on the search table
3682 -- on the mirror table. We will use the exception handler,
3683 -- g_index_columns_existed (ORA-01408) and g_name_already_used
3684 -- (ORA-00955) to take care of the indexes that already exist.
3685 --
3686 -- Since the search and mirror table exchange roles constantly, we
3687 -- need to do this process both ways by reversing the procedure
3688 -- described above.
3689 -- ------------------------------------------------------------------
3690 Create_Indexes(p_search_table, p_mirror_table, p_pv_schema_owner);
3691 Create_Indexes(p_mirror_table, p_search_table, p_pv_schema_owner);
3692
3693
3694 -- ------------------------------------------------------------
3695 -- Enable primary/unique constraints on the mirror table.
3696 -- ------------------------------------------------------------
3697 FOR x IN c_pk_unique_constraints(p_mirror_table, p_pv_schema_owner) LOOP
3698 l_index_ddl_stmt := 'ALTER TABLE ' || p_pv_schema_owner || '.' ||
3699 p_mirror_table || ' MODIFY CONSTRAINT ' ||
3700 x.constraint_name || ' ENABLE';
3701
3702 EXECUTE IMMEDIATE l_index_ddl_stmt;
3703 END LOOP;
3704 END Enable_Create_Indexes;
3705 -- ======================End of Enable_Create_Indexes============================
3706
3707
3708 --=============================================================================+
3709 --| Private Function |
3710 --| |
3711 --| Create_Indexes |
3712 --| |
3713 --| Parameters |
3714 --| IN |
3715 --| OUT |
3716 --| |
3717 --| |
3718 --| NOTES: |
3719 --| |
3720 --| HISTORY |
3721 --| |
3722 --==============================================================================
3723 PROCEDURE Create_Indexes(
3724 p_table1 IN VARCHAR2,
3725 p_table2 IN VARCHAR2,
3726 p_pv_schema_owner IN VARCHAR2
3727 )
3728 IS
3729 -- ---------------------------------------------------------------------------
3730 -- Retrieve indexes for a table.
3731 -- ---------------------------------------------------------------------------
3732 CURSOR c_indexes (pc_table_name IN VARCHAR2,
3733 pc_pv_schema_owner IN VARCHAR2)
3734 IS
3735 -- -------------------------------------------------------------------------
3736 -- The use of dbms_metadata requires invoker rights because roles are not
3737 -- enabled during the execution of a definer rights procedure.
3738 -- For this reason, we added "AUTHID CURRENT_USER" to the package
3739 -- (pvxvcons.pls). If this package is not made invoker rights enabled,
3740 -- Oracle will produce the following error when dbms_metadata.get_ddl
3741 -- is trying to extract DDL out of a non-APPS schema.
3742 --
3743 -- e.g.
3744 -- ORA-31603: object "PV_SEARCH_ATTR_VALUES_U1" of type INDEX not found
3745 -- in schema "PV"
3746 -- -------------------------------------------------------------------------
3747 SELECT index_name,
3748 dbms_metadata.get_ddl('INDEX', index_name, owner) ind_def
3749 FROM dba_indexes
3750 WHERE table_name = pc_table_name AND
3751 table_owner = pc_pv_schema_owner AND
3752 owner = pc_pv_schema_owner
3753 ORDER BY index_name;
3754
3755
3756 l_index_ddl_stmt VARCHAR2(4000);
3757
3758 BEGIN
3759 FOR x IN c_indexes(p_table1, p_pv_schema_owner) LOOP
3760 BEGIN
3761 l_index_ddl_stmt := REPLACE(x.ind_def, '"' || p_table1 || '"',
3762 '"' || p_table2 || '"');
3763
3764 IF (INSTR(l_index_ddl_stmt, '_M"') > 0) THEN
3765 l_index_ddl_stmt := REPLACE(l_index_ddl_stmt, x.index_name,
3766 SUBSTR(x.index_name, 1, LENGTH(x.index_name) - 2));
3767
3768 ELSE
3769 l_index_ddl_stmt := REPLACE(l_index_ddl_stmt, x.index_name, x.index_name || '_M');
3770 END IF;
3771
3772 EXECUTE IMMEDIATE l_index_ddl_stmt;
3773
3774 EXCEPTION
3775 -- ----------------------------------------------------------------
3776 -- If the index already exists, go to the next index.
3777 -- ----------------------------------------------------------------
3778 WHEN g_index_columns_existed THEN
3779 null;
3780
3781 WHEN g_name_already_used THEN
3782 null;
3783
3784 END;
3785 END LOOP;
3786
3787
3788 EXCEPTION
3789 WHEN g_e_definer_rights THEN
3790 Debug('Definer Rights.......................................');
3791 Debug(SQLCODE || ':::' || SQLERRM);
3792
3793
3794 END Create_Indexes;
3795 -- ==========================End of Create_Indexes===============================
3796
3797
3798
3799 --=============================================================================+
3800 --| Private Function |
3801 --| |
3802 --| Recompile_Dependencies |
3803 --| |
3804 --| Parameters |
3805 --| IN |
3806 --| OUT |
3807 --| |
3808 --| |
3809 --| NOTES: |
3810 --| |
3811 --| HISTORY |
3812 --| |
3813 --==============================================================================
3814 PROCEDURE Recompile_Dependencies(
3815 p_referenced_type IN VARCHAR2,
3816 p_referenced_name1 IN VARCHAR2,
3817 p_referenced_name2 IN VARCHAR2,
3818 p_api_package_name IN VARCHAR2
3819 )
3820 IS
3821 CURSOR c IS
3822 SELECT owner, name, type
3823 FROM dba_dependencies
3824 WHERE referenced_type = p_referenced_type AND
3825 referenced_name IN (p_referenced_name1, p_referenced_name2) AND
3826 owner = g_apps_schema;
3827
3828 l_ddl_str VARCHAR2(300);
3829 l_start NUMBER;
3830 l_start2 NUMBER;
3831
3832 BEGIN
3833 l_start := dbms_utility.get_time;
3834
3835 -- -----------------------------------------------------------------------
3836 -- Determine "APPS" schema.
3837 -- -----------------------------------------------------------------------
3838 IF (g_apps_schema IS NULL) THEN
3839 FOR x IN (SELECT user FROM dual) LOOP
3840 g_apps_schema := x.user;
3841 END LOOP;
3842 END IF;
3843
3844
3845 FOR x IN C LOOP
3846 IF (x.name <> p_api_package_name AND
3847 x.owner = g_apps_schema AND
3848 x.type = 'PACKAGE BODY')
3849 THEN
3850 FOR y IN (
3851 SELECT owner, object_name
3852 FROM dba_objects
3853 WHERE owner = x.owner AND
3854 object_name = x.name AND
3855 object_type = 'PACKAGE BODY' AND
3856 status = 'INVALID')
3857 LOOP
3858 l_start2 := dbms_utility.get_time;
3859 l_ddl_str := 'ALTER PACKAGE ' || y.owner || '.' || y.object_name ||
3860 ' COMPILE BODY';
3861 Debug(l_ddl_str);
3862
3863 BEGIN
3864 EXECUTE IMMEDIATE l_ddl_str;
3865 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start2) || ' hsec');
3866
3867 EXCEPTION
3868 WHEN OTHERS THEN
3869 Debug(SQLCODE || ':::' || SQLERRM);
3870 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start2) || ' hsec');
3871 END;
3872 END LOOP;
3873 END IF;
3874 END LOOP;
3875
3876 Debug('Elapsed Time (Recompile_Dependencies): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
3877 END Recompile_Dependencies;
3878 -- ==========================End of Recompile_Dependencies=======================
3879
3880
3881 END PV_CONTEXT_VALUES;