[Home] [Help]
PACKAGE BODY: APPS.PV_CONTEXT_VALUES
Source
1 package body PV_CONTEXT_VALUES AS
2 /* $Header: pvxvconb.pls 120.16 2011/08/26 00:17:32 hekkiral ship $ */
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 --Hekkiral Added for Online patching feature of 12.2. The Synonym now points to Edition view instead of table.
886
887 CURSOR c_evtable(pc_owner IN VARCHAR2,pc_view_name IN VARCHAR2) IS
888 select ev.table_name
889 from dba_editioning_views ev
890 where ev.owner = pc_owner and
891 ev.view_name = replace(pc_view_name, '$', '#');
892
893 -- -----------------------------------------------------------------------
894 -- Local Variables
895 -- -----------------------------------------------------------------------
896 l_partner_sql VARCHAR2(32000);
897 l_num_of_partners NUMBER;
898 l_num_of_blocks NUMBER;
899 l_avg_length NUMBER;
900
901 BEGIN
902 p_end_refresh_flag := FALSE;
903
904 IF (p_log_to_file = 'Y') THEN
905 g_log_to_file := 'Y';
906
907 ELSE
908 g_log_to_file := 'N';
909 END IF;
910
911
912 -- -----------------------------------------------------------------------
913 -- Reset the refresh type. If the refresh type is incremental, but the
914 -- last incremental refresh timestamp is NULL, set the refresh type to
915 -- g_incr_full_refresh ('INCR-FULL'). This means that we will use full
916 -- refresh method to perform refresh, but we will still update incremental
917 -- refresh timestamp.
918 -- -----------------------------------------------------------------------
919 IF ((p_refresh_type = g_incr_refresh) AND
920 (p_last_incr_refresh_str IS NULL))
921 THEN
922 Debug('Setting the refresh type to FULL since there is no prior refresh.');
923 p_out_refresh_type := g_incr_full_refresh;
924
925 ELSE
926 p_out_refresh_type := p_refresh_type;
927 END IF;
928
929 -- -----------------------------------------------------------------------
930 -- For incremental refresh, an Oracle temporary table must exist.
931 -- If it doesn't the refresh type will be changed to a full refresh.
932 -- -----------------------------------------------------------------------
933 IF ((p_out_refresh_type = g_incr_refresh) AND (p_partner_id_temp_table IS NULL)) THEN
934 Debug('-- ************************************************************************ --');
935 Debug('-- No Oracle temporary table provided. Incremental refresh will not proceed --');
936 Debug('-- A full refresh will be performed instead. --');
937 Debug('-- ************************************************************************ --');
938 p_out_refresh_type := g_full_refresh;
939 END IF;
940
941
942 -- -----------------------------------------------------------------------
943 -- Determine "APPS" schema.
944 -- -----------------------------------------------------------------------
945 IF (g_apps_schema IS NULL) THEN
946 FOR x IN (SELECT user FROM dual) LOOP
947 g_apps_schema := x.user;
948 END LOOP;
949 END IF;
950
951
952 -- -----------------------------------------------------------------------
953 -- Determine "PV" schema.
954 -- -----------------------------------------------------------------------
955 IF (p_pv_schema_name IS NULL) THEN
956 FOR x IN c_pv_schema LOOP
957 p_pv_schema_name := x.oracle_username;
958 END LOOP;
959 END IF;
960
961 -- -----------------------------------------------------------------------
962 -- Synonym recovery: recovers the synonyms of the search and the mirror
963 -- table in the event of a system crash in the previous concurrent
964 -- program run.
965 -- -----------------------------------------------------------------------
966 Debug('Synonym Recovery..................................................');
967 FOR x IN c_synonyms(p_synonym_name) LOOP
968 IF (x.count = 0) THEN
969 Debug('RENAME ' || p_temp_synonym_name || ' TO ' || p_synonym_name);
970
971 EXECUTE IMMEDIATE
972 'RENAME ' || p_temp_synonym_name || ' TO ' || p_synonym_name;
973 END IF;
974 END LOOP;
975
976 FOR x IN c_synonyms(p_mirror_synonym_name) LOOP
977 IF (x.count = 0) THEN
978 Debug('RENAME ' || p_temp_synonym_name || ' TO ' || p_mirror_synonym_name);
979
980 EXECUTE IMMEDIATE
981 'RENAME ' || p_temp_synonym_name || ' TO ' || p_mirror_synonym_name;
982 END IF;
983 END LOOP;
984
985 -- -----------------------------------------------------------------------
986 -- Retrieve the underlying tables of the search and the mirror table.
987 -- -----------------------------------------------------------------------
988 Debug('Retrieving the underlying tables of the synonyms...................');
989 FOR x IN c_underlying_tables LOOP
990 IF (x.synonym_name = p_synonym_name) THEN
991 p_search_table := x.table_name;
992
993 ELSIF (x.synonym_name = p_mirror_synonym_name) THEN
994 p_mirror_table := x.table_name;
995 END IF;
996 END LOOP;
997
998 --Hekkiral Added for Online patching feature of 12.2. The Synonym now points to Edition view instead of table.
999
1000 Debug(p_mirror_table || '.................................');
1001 Open c_evtable(p_pv_schema_name,p_mirror_table);
1002 FETCH c_evtable into p_mirror_table;
1003 Close c_evtable;
1004 Debug(p_mirror_table || '.................................');
1005
1006 Debug(p_search_table || '.................................');
1007 Open c_evtable(p_pv_schema_name,p_search_table);
1008 FETCH c_evtable into p_search_table;
1009 Close c_evtable;
1010 Debug(p_search_table || '.................................');
1011
1012
1013 -- =======================================================================
1014 -- Set the appropriate settings/parameters/attributes for different
1015 -- refresh types.
1016 -- =======================================================================
1017 IF ((p_out_refresh_type = g_incr_refresh) AND (p_temp_table_processed)) THEN
1018 -- ---------------------------------------------------------
1019 -- Set partner SQL
1020 -- ---------------------------------------------------------
1021 l_partner_sql :=
1022 'SELECT partner_id
1023 FROM pv_partner_profiles pvpp
1024 WHERE pvpp.status = ''A'' AND
1025 partner_resource_id IS NOT NULL AND
1026 creation_date >= :last_incr_refresh ';
1027
1028 -- ---------------------------------------------------------
1029 -- Insert the list of new partners into the temporary table.
1030 -- ---------------------------------------------------------
1031 Debug('Insert the list of new partners into the temporary table.............');
1032 EXECUTE IMMEDIATE
1033 'TRUNCATE TABLE ' || p_partner_id_temp_table;
1034
1035 EXECUTE IMMEDIATE
1036 'INSERT INTO ' || p_partner_id_temp_table || ' ' ||
1037 l_partner_sql
1038 USING TO_DATE(NVL(p_last_incr_refresh_str, '12-31-1900 00:00:01'),
1039 'MM-DD-YYYY HH24:MI:SS');
1040
1041 -- ---------------------------------------------------------
1042 -- If there are no new partners, mark the flag for exiting
1043 -- the program.
1044 -- ---------------------------------------------------------
1045 IF (SQL%ROWCOUNT = 0) THEN
1046 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1047 p_msg_name => 'PV_NO_NEW_PARTNERS');
1048
1049 p_end_refresh_flag := TRUE;
1050 RETURN;
1051
1052 ELSE
1053 Debug(SQL%ROWCOUNT || ' new partners found in the system.');
1054
1055 /* -------------------------------------------------------------
1056 Debug('.');
1057 Debug('Partner ID' || ' ' || 'Partner Name');
1058 Debug('----------' || ' ' || '-------------------------------');
1059
1060 For x IN (SELECT a.partner_id, c.party_name
1061 FROM pv_partner_id_session a,
1062 pv_partner_profiles b,
1063 hz_parties c
1064 WHERE a.partner_id = b.partner_id AND
1065 b.partner_party_id = c.party_id)
1066 LOOP
1067 Debug(LPAD(TO_CHAR(x.partner_id), 12) || ' ' || x.party_name);
1068 END LOOP;
1069 Debug('.');
1070 * -------------------------------------------------------------- */
1071 END IF;
1072
1073
1074 l_num_of_partners := SQL%ROWCOUNT;
1075 l_avg_length := 7;
1076 l_num_of_blocks := Compute_Num_of_Blocks(l_num_of_partners, l_avg_length);
1077
1078 -- -------------------------------------------------------------
1079 -- Set statistics for the temporary table. Since Oracle 8i does not
1080 -- generate statistics on a temporary table even if a table is
1081 -- analyzed.
1082 -- -------------------------------------------------------------
1083 Debug('Gathering statistics on the temporary table.........................');
1084 dbms_stats.set_table_stats(ownname => USER,
1085 tabname => p_partner_id_temp_table,
1086 numrows => l_num_of_partners,
1087 numblks => l_num_of_blocks,
1088 avgrlen => l_avg_length);
1089
1090 -- =======================================================================
1091 -- Full or 'INCR-FULL' Refresh
1092 -- =======================================================================
1093 ELSE
1094 Debug('Set the mirror table to NOLOGGING mode.........................');
1095 EXECUTE IMMEDIATE
1096 'ALTER TABLE ' || p_pv_schema_name || '.' || p_mirror_table ||
1097 ' NOLOGGING';
1098
1099 -- ---------------------------------------------------------
1100 -- Truncate the mirror table whether it's empty or not.
1101 -- ---------------------------------------------------------
1102 Debug('Truncate the mirror table.......................................');
1103 EXECUTE IMMEDIATE
1104 'TRUNCATE TABLE ' || p_pv_schema_name || '.' || p_mirror_table;
1105
1106 -- ---------------------------------------------------------
1107 -- * Make all non-unique indexes unusable.
1108 -- * Disable all primary and unique constraints, which,
1109 -- in effect, drop the associated unique indexes.
1110 -- * Drop all the remaining unique indexes.
1111 -- ---------------------------------------------------------
1112 Debug('Drop unique indexes and make nonunique indexes ' ||
1113 'unusable on the mirror table...');
1114
1115 Disable_Drop_Indexes(p_mirror_table, p_pv_schema_name);
1116 END IF;
1117
1118 END Pre_Processing;
1119 -- ===========================End of Pre_Processing==========================
1120
1121
1122
1123 --=============================================================================+
1124 --| Public Procedure |
1125 --| |
1126 --| Post_Processing |
1127 --| |
1128 --| Parameters |
1129 --| IN |
1130 --| OUT |
1131 --| |
1132 --| |
1133 --| NOTES: |
1134 --| |
1135 --| HISTORY |
1136 --| |
1137 --==============================================================================
1138 PROCEDURE Post_Processing (
1139 p_refresh_type IN VARCHAR2 := g_full_refresh,
1140 p_synonym_name IN VARCHAR2,
1141 p_mirror_synonym_name IN VARCHAR2,
1142 p_temp_synonym_name IN VARCHAR2,
1143 p_pv_schema_name IN VARCHAR2,
1144 p_search_table IN VARCHAR2,
1145 p_mirror_table IN VARCHAR2,
1146 p_incr_timestamp IN VARCHAR2,
1147 p_api_package_name IN VARCHAR2,
1148 p_module_name IN VARCHAR2,
1149 p_log_to_file IN VARCHAR2 := 'Y'
1150 )
1151 IS
1152 l_start NUMBER;
1153 l_elapsed_time NUMBER;
1154 l_ret_val BOOLEAN := FALSE;
1155
1156 BEGIN
1157 IF (p_log_to_file = 'Y') THEN
1158 g_log_to_file := 'Y';
1159
1160 ELSE
1161 g_log_to_file := 'N';
1162 END IF;
1163
1164 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
1165 -- --------------------------------------------------------------
1166 -- Recreate unique indexes and rebuild nonunique indexes.
1167 -- --------------------------------------------------------------
1168 l_start := dbms_utility.get_time;
1169 dbms_application_info.set_module(
1170 module_name => p_module_name,
1171 action_name => 'Post: Rebuild Indexes'
1172 );
1173 Debug('Recreate and rebuild indexes on the mirror table AND');
1174 Debug('Synch up indexes between the search and the mirror table...');
1175
1176 Enable_Create_Indexes(
1177 p_search_table,
1178 p_mirror_table,
1179 p_pv_schema_name
1180 );
1181
1182 -- --------------------------------------------------------------
1183 -- Analyze the mirror table.
1184 -- --------------------------------------------------------------
1185 dbms_application_info.set_module(
1186 module_name => p_module_name,
1187 action_name => 'Post: Analyze Tables'
1188 );
1189 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1190 l_start := dbms_utility.get_time;
1191 Debug('Analyze the mirror table...');
1192
1193 dbms_stats.gather_table_stats(
1194 ownname => p_pv_schema_name,
1195 tabname => p_mirror_table,
1196 estimate_percent => 10,
1197 method_opt => 'FOR ALL INDEXES'
1198 );
1199
1200 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1201 l_start := dbms_utility.get_time;
1202
1203 -- --------------------------------------------------------------
1204 -- Rename synonyms.
1205 -- --------------------------------------------------------------
1206 dbms_application_info.set_module(
1207 module_name => p_module_name,
1208 action_name => 'Post: Swapping Synonyms'
1209 );
1210 Debug('Synonym swapping and other post processing activities...');
1211
1212
1213 EXECUTE IMMEDIATE
1214 'RENAME ' || p_synonym_name || ' TO ' || p_temp_synonym_name;
1215
1216 EXECUTE IMMEDIATE
1217 'RENAME ' || p_mirror_synonym_name || ' TO ' || p_synonym_name;
1218
1219 EXECUTE IMMEDIATE
1220 'RENAME ' || p_temp_synonym_name || ' TO ' || p_mirror_synonym_name;
1221
1222 -- --------------------------------------------------------------
1223 -- Recompile invalid dependent package bodies.
1224 -- --------------------------------------------------------------
1225 dbms_application_info.set_module(
1226 module_name => g_module_name,
1227 action_name => 'Post: Compile Dependencies'
1228 );
1229 Debug('Recompile invalid dependent package bodies...');
1230
1231 Recompile_Dependencies(
1232 p_referenced_type => 'SYNONYM',
1233 p_referenced_name1 => p_synonym_name,
1234 p_referenced_name2 => p_mirror_synonym_name,
1235 p_api_package_name => p_api_package_name
1236 );
1237
1238
1239 -- --------------------------------------------------------------
1240 -- Truncate the "search" table.
1241 -- --------------------------------------------------------------
1242 dbms_application_info.set_module(
1243 module_name => p_module_name,
1244 action_name => 'Post: Truncate Table'
1245 );
1246 Debug('Truncate the search table...');
1247
1248 EXECUTE IMMEDIATE
1249 'TRUNCATE TABLE ' || p_pv_schema_name || '.' || p_search_table;
1250 END IF;
1251
1252 END Post_Processing;
1253 -- ===========================End of Post_Processing==========================
1254
1255
1256
1257 -- *****************************************************************************
1258 -- *****************************************************************************
1259 -- *****************************************************************************
1260 -- *****************************************************************************
1261
1262
1263
1264
1265 --=============================================================================+
1266 --| Private Procedure |
1267 --| |
1268 --| Debug |
1269 --| |
1270 --| Parameters |
1271 --| IN |
1272 --| OUT |
1273 --| |
1274 --| |
1275 --| NOTES: |
1276 --| |
1277 --| HISTORY |
1278 --| |
1279 --==============================================================================
1280 PROCEDURE Debug(
1281 p_msg_string IN VARCHAR2,
1282 p_msg_type IN VARCHAR2 := 'PV_DEBUG_MESSAGE'
1283 )
1284 IS
1285 BEGIN
1286 FND_MESSAGE.Set_Name('PV', p_msg_type);
1287 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
1288
1289 IF (g_log_to_file = 'N') THEN
1290 FND_MSG_PUB.Add;
1291
1292 ELSIF (g_log_to_file = 'Y') THEN
1293 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
1294 END IF;
1295 END Debug;
1296 -- =================================End of Debug================================
1297
1298
1299 --=============================================================================+
1300 --| Public Procedure |
1301 --| |
1302 --| Set_Message |
1303 --| |
1304 --| Parameters |
1305 --| IN |
1306 --| OUT |
1307 --| |
1308 --| |
1309 --| NOTES: |
1310 --| |
1311 --| HISTORY |
1312 --| |
1313 --==============================================================================
1314 PROCEDURE Set_Message(
1315 p_msg_level IN NUMBER,
1316 p_msg_name IN VARCHAR2,
1317 p_token1 IN VARCHAR2 := NULL,
1318 p_token1_value IN VARCHAR2 := NULL,
1319 p_token2 IN VARCHAR2 := NULL,
1320 p_token2_value IN VARCHAR2 := NULL,
1321 p_token3 IN VARCHAR2 := NULL,
1322 p_token3_value IN VARCHAR2 := NULL
1323 )
1324 IS
1325 BEGIN
1326 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level) THEN
1327 FND_MESSAGE.Set_Name('PV', p_msg_name);
1328 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
1329
1330 IF (p_token1 IS NOT NULL) THEN
1331 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
1332 END IF;
1333
1334 IF (p_token2 IS NOT NULL) THEN
1335 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
1336 END IF;
1337
1338 IF (p_token3 IS NOT NULL) THEN
1339 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
1340 END IF;
1341
1342 IF (g_log_to_file = 'N') THEN
1343 FND_MSG_PUB.Add;
1344
1345 ELSIF (g_log_to_file = 'Y') THEN
1346 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
1347 END IF;
1348 END IF;
1349 END Set_Message;
1350 -- ==============================End of Set_Message==============================
1351
1352
1353 --=============================================================================+
1354 --| Private Function |
1355 --| |
1356 --| Compute_Num_of_Blocks |
1357 --| |
1358 --| Parameters |
1359 --| IN |
1360 --| OUT |
1361 --| |
1362 --| |
1363 --| NOTES: |
1364 --| |
1365 --| HISTORY |
1366 --| |
1367 --==============================================================================
1368 FUNCTION Compute_Num_of_Blocks(
1369 p_num_of_rows IN NUMBER,
1370 p_avg_length IN NUMBER
1371 )
1372 RETURN NUMBER
1373 IS
1374 CURSOR c_block_size IS
1375 SELECT value
1376 FROM v$parameter
1377 WHERE name = 'db_block_size';
1378
1379 l_db_block_size NUMBER;
1380 l_data_size NUMBER;
1381
1382 -- ----------------------------------------------------------------
1383 -- The part of the block that is used for block overhead.
1384 -- Set it to 1/8 --> 0.125. (this is a guestimate value).
1385 -- ----------------------------------------------------------------
1386 l_overhead_ratio NUMBER := 0.125;
1387
1388 BEGIN
1389 l_data_size := p_num_of_rows * p_avg_length;
1390
1391 FOR x IN c_block_size LOOP
1392 l_db_block_size := TO_NUMBER(x.value);
1393 END LOOP;
1394
1395 RETURN (l_db_block_size - (l_db_block_size * l_overhead_ratio))/l_data_size;
1396
1397 END Compute_Num_of_Blocks;
1398 -- ===========================End of Compute_Num_of_Blocks=======================
1399
1400
1401
1402
1403
1404
1405 --=============================================================================+
1406 --| Private Function |
1407 --| |
1408 --| Insert_Functional_Expertise |
1409 --| |
1410 --| Parameters |
1411 --| IN |
1412 --| OUT |
1413 --| |
1414 --| |
1415 --| NOTES: |
1416 --| |
1417 --| HISTORY |
1418 --| |
1419 --==============================================================================
1420 PROCEDURE Insert_Functional_Expertise (
1421 p_refresh_type IN VARCHAR2,
1422 p_user_id IN NUMBER
1423 )
1424 IS
1425
1426 l_insert_header VARCHAR2(200);
1427 l_insert_body VARCHAR2(1000);
1428 l_ddl_str VARCHAR2(4000);
1429
1430 BEGIN
1431 dbms_application_info.set_module(
1432 module_name => g_module_name,
1433 action_name => 'Inside Attribute ID 1'
1434 );
1435
1436
1437
1438 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
1439 INSERT /*+ APPEND */
1440 INTO pv_search_attr_mirror (
1441 SEARCH_ATTR_VALUES_ID,
1442 PARTY_ID,
1443 ATTRIBUTE_ID,
1444 ATTR_TEXT,
1445 CREATION_DATE,
1446 CREATED_BY,
1447 LAST_UPDATE_DATE,
1448 LAST_UPDATED_BY,
1449 LAST_UPDATE_LOGIN ,
1450 OBJECT_Version_number
1451 )
1452 SELECT pv_search_attr_values_s.nextval,
1453 entity_id,
1454 1,
1455 attr_value,
1456 SYSDATE,
1457 p_user_id,
1458 SYSDATE,
1459 p_user_id,
1460 p_user_id,
1461 1.0
1462 FROM (
1463 SELECT DISTINCT
1464 a.entity_id,
1465 DENORM.child_id attr_value
1466 FROM pv_enty_attr_values a,
1467 pv_entity_attrs b,
1468 eni_prod_denorm_hrchy_v DENORM
1469 WHERE b.attribute_id = 1 AND
1470 a.latest_flag = 'Y' AND
1471 a.entity = 'PARTNER' AND
1472 a.attr_value = TO_CHAR(DENORM.parent_id) AND
1473 a.attribute_id = b.attribute_id AND
1474 b.entity = 'PARTNER' AND
1475 (b.last_refresh_date IS NULL OR
1476 b.refresh_frequency IS NULL OR
1477 b.refresh_frequency_uom IS NULL OR
1478 (b.last_refresh_date +
1479 DECODE(b.refresh_frequency_uom,
1480 'HOUR', b.refresh_frequency/24,
1481 'DAY', b.refresh_frequency,
1482 'WEEK', b.refresh_frequency * 7,
1483 'MONTH', ADD_MONTHS(TRUNC(NVL(b.last_refresh_date, SYSDATE), 'MM'),
1484 b.refresh_frequency)
1485 - NVL(b.last_refresh_date, SYSDATE)
1486 )
1487 ) <= SYSDATE)
1488 );
1489
1490
1491 Debug(SQL%ROWCOUNT || ' rows inserted.');
1492
1493 -- ---------------------------------------------------------------
1494 -- Update timestamp
1495 -- ---------------------------------------------------------------
1496 dbms_application_info.set_module(
1497 module_name => g_module_name,
1498 action_name => 'Timestamp Attribute ID 1'
1499 );
1500
1501 Update_Timestamp (
1502 p_attribute_id => 1,
1503 p_timestamp => SYSDATE
1504 );
1505
1506 COMMIT;
1507
1508 ELSE
1509 -- ----------------------------------------------------------------
1510 -- In an incremental refresh (new partners only refresh), make
1511 -- sure the records are not already in the search table before
1512 -- inserting the records.
1513 -- ----------------------------------------------------------------
1514 DELETE FROM pv_search_attr_values
1515 WHERE attribute_id = 1 AND
1516 party_id IN (SELECT partner_id FROM pv_partner_id_session);
1517
1518
1519 INSERT
1520 INTO pv_search_attr_values (
1521 SEARCH_ATTR_VALUES_ID,
1522 PARTY_ID,
1523 ATTRIBUTE_ID,
1524 ATTR_TEXT,
1525 CREATION_DATE,
1526 CREATED_BY,
1527 LAST_UPDATE_DATE,
1528 LAST_UPDATED_BY,
1529 LAST_UPDATE_LOGIN ,
1530 OBJECT_Version_number
1531 )
1532 SELECT pv_search_attr_values_s.nextval,
1533 entity_id,
1534 1,
1535 attr_value,
1536 SYSDATE,
1537 p_user_id,
1538 SYSDATE,
1539 p_user_id,
1540 p_user_id,
1541 1.0
1542 FROM (
1543 SELECT DISTINCT
1544 a.entity_id,
1545 DENORM.child_id attr_value
1546 FROM pv_enty_attr_values a,
1547 pv_partner_id_session b,
1548 eni_prod_denorm_hrchy_v DENORM
1549 WHERE a.attribute_id = 1 AND
1550 a.latest_flag = 'Y' AND
1551 a.entity = 'PARTNER' AND
1552 a.entity_id = b.partner_id AND
1553 a.attr_value = TO_CHAR(DENORM.parent_id));
1554
1555 Debug(SQL%ROWCOUNT || ' rows inserted.');
1556
1557 END IF;
1558
1559
1560 EXCEPTION
1561 WHEN others THEN
1562 Debug('Exception raised while inserting for "functional expertise" ' ||
1563 '(Attribute ID = 1)');
1564 Debug(SQLCODE);
1565 Debug(SQLERRM);
1566
1567 g_RETCODE := '1';
1568
1569 END Insert_Functional_Expertise;
1570 -- =======================End of Insert_Functional_Expertise====================
1571
1572
1573
1574 --=============================================================================+
1575 --| Private Function |
1576 --| |
1577 --| Insert_Internal |
1578 --| |
1579 --| Parameters |
1580 --| IN |
1581 --| OUT |
1582 --| |
1583 --| |
1584 --| NOTES: |
1585 --| |
1586 --| HISTORY |
1587 --| |
1588 --==============================================================================
1589 PROCEDURE Insert_Internal (
1590 p_refresh_type IN VARCHAR2,
1591 p_user_id IN NUMBER
1592 )
1593 IS
1594 -- ----------------------------------------------------------------------
1595 -- Local Variables
1596 -- ----------------------------------------------------------------------
1597 l_last_message VARCHAR2(30000);
1598 l_start NUMBER;
1599 l_total_start NUMBER;
1600 l_count NUMBER;
1601
1602 -- ----------------------------------------------------------------------
1603 -- Currency Attributes - Internal
1604 -- ----------------------------------------------------------------------
1605 CURSOR c_currency_attrs IS
1606 SELECT a.attribute_id, b.name
1607 FROM pv_entity_attrs a,
1608 pv_attributes_vl b
1609 WHERE b.attribute_id <> 1 AND
1610 a.attribute_id = b.attribute_id AND
1611 a.enabled_flag = 'Y' AND
1612 b.enabled_flag = 'Y' AND
1613 (b.enable_matching_flag = 'Y' OR
1614 a.display_external_value_flag = 'Y') AND
1615 a.entity = 'PARTNER' AND
1616 a.attr_data_type IN ('INTERNAL', 'INT_EXT')AND
1617 (a.last_refresh_date IS NULL OR
1618 a.refresh_frequency IS NULL OR
1619 a.refresh_frequency_uom IS NULL OR
1620 (last_refresh_date +
1621 DECODE(refresh_frequency_uom,
1622 'HOUR', refresh_frequency/24,
1623 'DAY', refresh_frequency,
1624 'WEEK', refresh_frequency * 7,
1625 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
1626 refresh_frequency)
1627 - NVL(last_refresh_date, SYSDATE)
1628 )
1629 ) <= SYSDATE) AND
1630 b.return_type = 'CURRENCY'
1631 ORDER BY a.attribute_id;
1632
1633 i NUMBER := 1;
1634 l_total_rows NUMBER := 0;
1635
1636 BEGIN
1637 -- *****************************************************************
1638 -- *****************************************************************
1639 -- Currency Attributes Refresh
1640 -- *****************************************************************
1641 -- *****************************************************************
1642 l_total_start := dbms_utility.get_time;
1643
1644 Debug('-- **********************************************************');
1645 Debug('-- Processing internal CURRENCY attributes...');
1646 Debug('-- **********************************************************');
1647
1648 dbms_application_info.set_module(
1649 module_name => g_module_name,
1650 action_name => 'Internal - CURRENCY'
1651 );
1652
1653 -- -----------------------------------------------------------------
1654 -- Process currency attributes one attribute at a time.
1655 -- Within each attribute, it's a all-or-nothing operation. If
1656 -- currency conversion fails for even one record, the whole operation
1657 -- will be "rolled back".
1658 -- -----------------------------------------------------------------
1659 FOR x IN c_currency_attrs LOOP
1660 BEGIN
1661 l_start := dbms_utility.get_time;
1662
1663 -- --------------------------------------------------------------
1664 -- Full Refresh
1665 -- --------------------------------------------------------------
1666 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
1667 INSERT /*+ APPEND */
1668 INTO pv_search_attr_mirror (
1669 SEARCH_ATTR_VALUES_ID,
1670 PARTY_ID,
1671 ATTRIBUTE_ID,
1672 ATTR_TEXT,
1673 ATTR_VALUE,
1674 CREATION_DATE,
1675 CREATED_BY,
1676 LAST_UPDATE_DATE,
1677 LAST_UPDATED_BY,
1678 LAST_UPDATE_LOGIN ,
1679 OBJECT_Version_number)
1680 SELECT pv_search_attr_values_s.nextval,
1681 entity_id,
1682 x.attribute_id,
1683 attr_text,
1684 attr_value,
1685 SYSDATE,
1686 p_user_id,
1687 SYSDATE,
1688 p_user_id,
1689 p_user_id,
1690 1.0
1691 FROM (
1692 SELECT DISTINCT a.entity_id,
1693 a.attr_value attr_text,
1694 pv_check_match_pub.Currency_Conversion(
1695 a.attr_value, g_common_currency) attr_value
1696 FROM pv_enty_attr_values a,
1697 pv_partner_profiles PV
1698 WHERE a.entity = 'PARTNER' AND
1699 a.latest_flag = 'Y' AND
1700 a.entity_id = PV.partner_id AND
1701 PV.partner_resource_id IS NOT NULL AND
1702 PV.status = 'A' AND
1703 a.attr_value IS NOT NULL AND
1704 a.attribute_id = x.attribute_id);
1705
1706 Debug('Processing Attribute "' || x.name || '" (Attribute ID = ' ||
1707 x.attribute_id || ')');
1708 Debug(SQL%ROWCOUNT || ' rows processed.');
1709
1710 l_total_rows := l_total_rows + SQL%ROWCOUNT;
1711
1712
1713 Update_Timestamp (
1714 p_attribute_id => x.attribute_id,
1715 p_timestamp => SYSDATE
1716 );
1717
1718 COMMIT;
1719
1720 -- --------------------------------------------------------------
1721 -- Partial Refresh
1722 -- --------------------------------------------------------------
1723 ELSE
1724 -- ----------------------------------------------------------------
1725 -- In an incremental refresh (new partners only refresh), make
1726 -- sure the records are not already in the search table before
1727 -- inserting the records.
1728 -- ----------------------------------------------------------------
1729 DELETE FROM pv_search_attr_values
1730 WHERE attribute_id = x.attribute_id AND
1731 party_id IN (SELECT partner_id FROM pv_partner_id_session);
1732
1733
1734 INSERT
1735 INTO pv_search_attr_values (
1736 SEARCH_ATTR_VALUES_ID,
1737 PARTY_ID,
1738 ATTRIBUTE_ID,
1739 ATTR_TEXT,
1740 ATTR_VALUE,
1741 CREATION_DATE,
1742 CREATED_BY,
1743 LAST_UPDATE_DATE,
1744 LAST_UPDATED_BY,
1745 LAST_UPDATE_LOGIN ,
1746 OBJECT_Version_number)
1747 SELECT pv_search_attr_values_s.nextval,
1748 entity_id,
1749 x.attribute_id,
1750 attr_text,
1751 attr_value,
1752 SYSDATE,
1753 p_user_id,
1754 SYSDATE,
1755 p_user_id,
1756 p_user_id,
1757 1.0
1758 FROM (
1759 SELECT DISTINCT a.entity_id,
1760 a.attr_value attr_text,
1761 pv_check_match_pub.Currency_Conversion(
1762 a.attr_value, g_common_currency) attr_value
1763 FROM pv_enty_attr_values a,
1764 pv_partner_id_session b
1765 WHERE a.entity = 'PARTNER' AND
1766 a.latest_flag = 'Y' AND
1767 a.attr_value IS NOT NULL AND
1768 a.entity_id = b.partner_id AND
1769 a.attribute_id = x.attribute_id);
1770
1771 Debug('Processing Attribute "' || x.name || '" (Attribute ID = ' ||
1772 x.attribute_id || ')');
1773 Debug(SQL%ROWCOUNT || ' rows processed.');
1774
1775 l_total_rows := l_total_rows + SQL%ROWCOUNT;
1776 END IF;
1777
1778 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1779
1780 EXCEPTION
1781 WHEN FND_API.G_EXC_ERROR THEN
1782 -- -------------------------------------------------------------------
1783 -- Retrieve the last message from the message queue which
1784 -- contains the exception raised by the called program
1785 -- e.g. currency_conversion
1786 -- -------------------------------------------------------------------
1787 -- Reset the pointer to the last message of the queue
1788 fnd_msg_pub.reset(fnd_msg_pub.G_LAST);
1789
1790 -- -------------------------------------------------------------------
1791 -- Go back to the second to last message which contains the message
1792 -- raised by the called program (e.g. currency_conversion)
1793 -- -------------------------------------------------------------------
1794 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
1795 p_encoded => FND_API.g_false);
1796 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
1797 p_encoded => FND_API.g_false);
1798 Debug(l_last_message);
1799
1800 Debug('Attribute ID: ' || x.attribute_id);
1801 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
1802
1803 g_RETCODE := '1';
1804
1805 -- -------------------------------------------------------------------
1806 -- If there is an exception with curreny_conversion, we need to "roll"
1807 -- back changes. In our case, this means copy from the search table
1808 -- and insert into the mirror table.
1809 -- -------------------------------------------------------------------
1810 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
1811 INSERT /*+ APPEND */ INTO pv_search_attr_mirror
1812 (SEARCH_ATTR_VALUES_ID,
1813 PARTY_ID,
1814 SHORT_NAME,
1815 ATTR_TEXT,
1816 CREATION_DATE,
1817 CREATED_BY,
1818 LAST_UPDATE_DATE,
1819 LAST_UPDATE_LOGIN,
1820 OBJECT_VERSION_NUMBER,
1821 LAST_UPDATED_BY,
1822 SECURITY_GROUP_ID,
1823 ATTRIBUTE_ID,
1824 ATTR_VALUE
1825 )
1826 SELECT SEARCH_ATTR_VALUES_ID,
1827 PARTY_ID,
1828 SHORT_NAME,
1829 ATTR_TEXT,
1830 CREATION_DATE,
1831 CREATED_BY,
1832 LAST_UPDATE_DATE,
1833 LAST_UPDATE_LOGIN,
1834 OBJECT_VERSION_NUMBER,
1835 LAST_UPDATED_BY,
1836 SECURITY_GROUP_ID,
1837 ATTRIBUTE_ID,
1838 ATTR_VALUE
1839 FROM pv_search_attr_values
1840 WHERE attribute_id = x.attribute_id;
1841
1842 COMMIT;
1843 END IF;
1844 END;
1845 END LOOP;
1846
1847
1848
1849 -- *****************************************************************
1850 -- *****************************************************************
1851 -- Full Refresh - non-Currency Attributes
1852 -- *****************************************************************
1853 -- *****************************************************************
1854 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
1855 -- --------------------------------------------------------------------------
1856 -- Process NUMBER return_type.
1857 -- --------------------------------------------------------------------------
1858 l_start := dbms_utility.get_time;
1859 Debug('-- **********************************************************');
1860 Debug('-- Processing Internal Number attributes...');
1861 Debug('-- **********************************************************');
1862
1863 dbms_application_info.set_module(
1864 module_name => g_module_name,
1865 action_name => 'Internal - NUMBER'
1866 );
1867
1868 INSERT /*+ APPEND */
1869 INTO pv_search_attr_mirror (
1870 SEARCH_ATTR_VALUES_ID,
1871 PARTY_ID,
1872 ATTRIBUTE_ID,
1873 ATTR_VALUE,
1874 CREATION_DATE,
1875 CREATED_BY,
1876 LAST_UPDATE_DATE,
1877 LAST_UPDATED_BY,
1878 LAST_UPDATE_LOGIN ,
1879 OBJECT_Version_number)
1880 SELECT pv_search_attr_values_s.nextval,
1881 entity_id,
1882 attribute_id,
1883 TO_NUMBER(attr_value),
1884 SYSDATE,
1885 p_user_id,
1886 SYSDATE,
1887 p_user_id,
1888 p_user_id,
1889 1.0
1890 FROM (
1891 SELECT DISTINCT a.entity_id, a.attr_value attr_value, a.attribute_id
1892 FROM pv_enty_attr_values a,
1893 pv_partner_profiles PV
1894 WHERE a.entity = 'PARTNER' AND
1895 a.latest_flag = 'Y' AND
1896 a.attr_value IS NOT NULL AND
1897 a.entity_id = PV.partner_id AND
1898 PV.partner_resource_id IS NOT NULL AND
1899 PV.status = 'A' AND
1900 a.attribute_id IN (
1901 SELECT a.attribute_id
1902 FROM pv_entity_attrs a,
1903 pv_attributes_b b
1904 WHERE b.attribute_id <> 1 AND
1905 a.attribute_id = b.attribute_id AND
1906 a.enabled_flag = 'Y' AND
1907 b.enabled_flag = 'Y' AND
1908 b.enable_matching_flag = 'Y' AND
1909 a.entity = 'PARTNER' AND
1910 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
1911 (a.last_refresh_date IS NULL OR
1912 a.refresh_frequency IS NULL OR
1913 a.refresh_frequency_uom IS NULL OR
1914 (last_refresh_date +
1915 DECODE(refresh_frequency_uom,
1916 'HOUR', refresh_frequency/24,
1917 'DAY', refresh_frequency,
1918 'WEEK', refresh_frequency * 7,
1919 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
1920 refresh_frequency)
1921 - NVL(last_refresh_date, SYSDATE)
1922 )
1923 ) <= SYSDATE) AND
1924 b.return_type = 'NUMBER'));
1925
1926 Debug(SQL%ROWCOUNT || ' rows processed.');
1927 l_total_rows := l_total_rows + SQL%ROWCOUNT;
1928
1929 -- --------------------------------------------------------------------------
1930 -- Update timestamp
1931 -- --------------------------------------------------------------------------
1932 FOR x IN (SELECT a.attribute_id
1933 FROM pv_entity_attrs a,
1934 pv_attributes_b b
1935 WHERE b.attribute_id <> 1 AND
1936 a.attribute_id = b.attribute_id AND
1937 a.enabled_flag = 'Y' AND
1938 b.enabled_flag = 'Y' AND
1939 (b.enable_matching_flag = 'Y' OR
1940 a.display_external_value_flag = 'Y') AND
1941 a.entity = 'PARTNER' AND
1942 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
1943 (a.last_refresh_date IS NULL OR
1944 a.refresh_frequency IS NULL OR
1945 a.refresh_frequency_uom IS NULL OR
1946 (last_refresh_date +
1947 DECODE(refresh_frequency_uom,
1948 'HOUR', refresh_frequency/24,
1949 'DAY', refresh_frequency,
1950 'WEEK', refresh_frequency * 7,
1951 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
1952 refresh_frequency)
1953 - NVL(last_refresh_date, SYSDATE)
1954 )
1955 ) <= SYSDATE) AND
1956 b.return_type = 'NUMBER')
1957 LOOP
1958 Update_Timestamp (
1959 p_attribute_id => x.attribute_id,
1960 p_timestamp => SYSDATE
1961 );
1962 END LOOP;
1963
1964 COMMIT;
1965
1966 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1967
1968
1969 -- --------------------------------------------------------------------------
1970 -- Process return_types other than NUMBER and CURRENCY.
1971 -- --------------------------------------------------------------------------
1972 l_start := dbms_utility.get_time;
1973 Debug('-- **********************************************************');
1974 Debug('-- Processing internal OTHER attributes...');
1975 Debug('-- **********************************************************');
1976 dbms_application_info.set_module(
1977 module_name => g_module_name,
1978 action_name => 'Internal - OTHER'
1979 );
1980
1981 -- ----------------------------------------------------------------------
1982 -- In R12, there is a concept of primary partner type and secondary
1983 -- partner type (attribute_id = 3). A primary partner type is indicated
1984 -- by marking pv_enty_attr_values.attr_value_extn as 'Y'. Only primary
1985 -- partner type of a partner needs to be populated in the search table.
1986 -- ----------------------------------------------------------------------
1987 INSERT /*+ APPEND */
1988 INTO pv_search_attr_mirror (
1989 SEARCH_ATTR_VALUES_ID,
1990 PARTY_ID,
1991 ATTRIBUTE_ID,
1992 ATTR_TEXT,
1993 CREATION_DATE,
1994 CREATED_BY,
1995 LAST_UPDATE_DATE,
1996 LAST_UPDATED_BY,
1997 LAST_UPDATE_LOGIN ,
1998 OBJECT_Version_number)
1999 SELECT pv_search_attr_values_s.nextval,
2000 entity_id,
2001 attribute_id,
2002 attr_value,
2003 SYSDATE,
2004 p_user_id,
2005 SYSDATE,
2006 p_user_id,
2007 p_user_id,
2008 1.0
2009 FROM (
2010 SELECT DISTINCT a.entity_id, a.attr_value attr_value, a.attribute_id
2011 FROM pv_enty_attr_values a,
2012 pv_partner_profiles PV
2013 WHERE a.entity = 'PARTNER' AND
2014 a.latest_flag = 'Y' AND
2015 a.attr_value IS NOT NULL AND
2016 DECODE(a.attribute_id, 3, attr_value_extn, 'Y') = 'Y' AND
2017 a.entity_id = PV.partner_id AND
2018 PV.partner_resource_id IS NOT NULL AND
2019 PV.status = 'A' AND
2020 a.attribute_id IN (
2021 SELECT a.attribute_id
2022 FROM pv_entity_attrs a,
2023 pv_attributes_b b
2024 WHERE b.attribute_id <> 1 AND
2025 a.attribute_id = b.attribute_id AND
2026 a.enabled_flag = 'Y' AND
2027 b.enabled_flag = 'Y' AND
2028 (b.enable_matching_flag = 'Y' OR
2029 a.display_external_value_flag = 'Y') AND
2030 a.entity = 'PARTNER' AND
2031 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
2032 (a.last_refresh_date IS NULL OR
2033 a.refresh_frequency IS NULL OR
2034 a.refresh_frequency_uom IS NULL OR
2035 (last_refresh_date +
2036 DECODE(refresh_frequency_uom,
2037 'HOUR', refresh_frequency/24,
2038 'DAY', refresh_frequency,
2039 'WEEK', refresh_frequency * 7,
2040 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
2041 refresh_frequency)
2042 - NVL(last_refresh_date, SYSDATE)
2043 )
2044 ) <= SYSDATE) AND
2045 b.return_type NOT IN ('NUMBER', 'CURRENCY')));
2046
2047 Debug(SQL%ROWCOUNT || ' rows processed.');
2048 l_total_rows := l_total_rows + SQL%ROWCOUNT;
2049
2050 -- --------------------------------------------------------------------------
2051 -- Update timestamp
2052 -- --------------------------------------------------------------------------
2053 FOR x IN (SELECT a.attribute_id
2054 FROM pv_entity_attrs a,
2055 pv_attributes_b b
2056 WHERE b.attribute_id <> 1 AND
2057 a.attribute_id = b.attribute_id AND
2058 a.enabled_flag = 'Y' AND
2059 b.enabled_flag = 'Y' AND
2060 (b.enable_matching_flag = 'Y' OR
2061 a.display_external_value_flag = 'Y') AND
2062 a.entity = 'PARTNER' AND
2063 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
2064 (a.last_refresh_date IS NULL OR
2065 a.refresh_frequency IS NULL OR
2066 a.refresh_frequency_uom IS NULL OR
2067 (last_refresh_date +
2068 DECODE(refresh_frequency_uom,
2069 'HOUR', refresh_frequency/24,
2070 'DAY', refresh_frequency,
2071 'WEEK', refresh_frequency * 7,
2072 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
2073 refresh_frequency)
2074 - NVL(last_refresh_date, SYSDATE)
2075 )
2076 ) <= SYSDATE) AND
2077 b.return_type NOT IN ('NUMBER', 'CURRENCY'))
2078 LOOP
2079 Update_Timestamp (
2080 p_attribute_id => x.attribute_id,
2081 p_timestamp => SYSDATE
2082 );
2083 END LOOP;
2084
2085 COMMIT;
2086
2087 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
2088
2089
2090
2091 -- *****************************************************************
2092 -- *****************************************************************
2093 -- Partial Refresh - non-Currency Attributes
2094 -- *****************************************************************
2095 -- *****************************************************************
2096 ELSE
2097 -- --------------------------------------------------------------------------
2098 -- Process Internal NUMBER return_type.
2099 -- --------------------------------------------------------------------------
2100 l_start := dbms_utility.get_time;
2101 Debug('-- **********************************************************');
2102 Debug('-- Processing internal NUMBER attributes...');
2103 Debug('-- **********************************************************');
2104 dbms_application_info.set_module(
2105 module_name => g_module_name,
2106 action_name => 'Internal - NUMBER'
2107 );
2108
2109 -- ----------------------------------------------------------------
2110 -- In an incremental refresh (new partners only refresh), make
2111 -- sure the records are not already in the search table before
2112 -- inserting the records.
2113 -- ----------------------------------------------------------------
2114 DELETE FROM pv_search_attr_values
2115 WHERE party_id IN (SELECT partner_id FROM pv_partner_id_session) AND
2116 attribute_id IN (
2117 SELECT a.attribute_id
2118 FROM pv_entity_attrs a,
2119 pv_attributes_b b
2120 WHERE b.attribute_id <> 1 AND
2121 a.attribute_id = b.attribute_id AND
2122 a.enabled_flag = 'Y' AND
2123 b.enabled_flag = 'Y' AND
2124 (b.enable_matching_flag = 'Y' OR
2125 a.display_external_value_flag = 'Y') AND
2126 a.entity = 'PARTNER' AND
2127 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
2128 b.return_type = 'NUMBER');
2129
2130
2131 INSERT
2132 INTO pv_search_attr_values (
2133 SEARCH_ATTR_VALUES_ID,
2134 PARTY_ID,
2135 ATTRIBUTE_ID,
2136 ATTR_VALUE,
2137 CREATION_DATE,
2138 CREATED_BY,
2139 LAST_UPDATE_DATE,
2140 LAST_UPDATED_BY,
2141 LAST_UPDATE_LOGIN ,
2142 OBJECT_Version_number)
2143 SELECT pv_search_attr_values_s.nextval,
2144 entity_id,
2145 attribute_id,
2146 TO_NUMBER(attr_value),
2147 SYSDATE,
2148 p_user_id,
2149 SYSDATE,
2150 p_user_id,
2151 p_user_id,
2152 1.0
2153 FROM (
2154 SELECT DISTINCT a.entity_id, attr_value attr_value, attribute_id
2155 FROM pv_enty_attr_values a,
2156 pv_partner_id_session b
2157 WHERE a.entity = 'PARTNER' AND
2158 a.entity_id = b.partner_id AND
2159 latest_flag = 'Y' AND
2160 attr_value IS NOT NULL AND
2161 attribute_id IN (
2162 SELECT a.attribute_id
2163 FROM pv_entity_attrs a,
2164 pv_attributes_b b
2165 WHERE b.attribute_id <> 1 AND
2166 a.attribute_id = b.attribute_id AND
2167 a.enabled_flag = 'Y' AND
2168 b.enabled_flag = 'Y' AND
2169 (b.enable_matching_flag = 'Y' OR
2170 a.display_external_value_flag = 'Y') AND
2171 a.entity = 'PARTNER' AND
2172 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
2173 b.return_type = 'NUMBER'));
2174
2175 Debug(SQL%ROWCOUNT || ' rows processed.');
2176 l_total_rows := l_total_rows + SQL%ROWCOUNT;
2177
2178 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
2179
2180
2181 -- --------------------------------------------------------------------------
2182 -- Process return_types other than NUMBER and CURRENCY.
2183 -- --------------------------------------------------------------------------
2184 l_start := dbms_utility.get_time;
2185 Debug('-- **********************************************************');
2186 Debug('-- Processing internal OTHER attributes...');
2187 Debug('-- **********************************************************');
2188 dbms_application_info.set_module(
2189 module_name => g_module_name,
2190 action_name => 'Internal - OTHER'
2191 );
2192
2193 -- ----------------------------------------------------------------
2194 -- In an incremental refresh (new partners only refresh), make
2195 -- sure the records are not already in the search table before
2196 -- inserting the records.
2197 -- ----------------------------------------------------------------
2198 DELETE FROM pv_search_attr_values
2199 WHERE party_id IN (SELECT partner_id FROM pv_partner_id_session) AND
2200 attribute_id IN (
2201 SELECT a.attribute_id
2202 FROM pv_entity_attrs a,
2203 pv_attributes_b b
2204 WHERE b.attribute_id <> 1 AND
2205 a.attribute_id = b.attribute_id AND
2206 a.enabled_flag = 'Y' AND
2207 b.enabled_flag = 'Y' AND
2208 (b.enable_matching_flag = 'Y' OR
2209 a.display_external_value_flag = 'Y') AND
2210 a.entity = 'PARTNER' AND
2211 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
2212 b.return_type NOT IN ('NUMBER', 'CURRENCY'));
2213
2214 -- ----------------------------------------------------------------------
2215 -- In R12, there is a concept of primary partner type and secondary
2216 -- partner type (attribute_id = 3). A primary partner type is indicated
2217 -- by marking pv_enty_attr_values.attr_value_extn as 'Y'. Only primary
2218 -- partner type of a partner needs to be populated in the search table.
2219 -- ----------------------------------------------------------------------
2220
2221 INSERT
2222 INTO pv_search_attr_values (
2223 SEARCH_ATTR_VALUES_ID,
2224 PARTY_ID,
2225 ATTRIBUTE_ID,
2226 ATTR_TEXT,
2227 CREATION_DATE,
2228 CREATED_BY,
2229 LAST_UPDATE_DATE,
2230 LAST_UPDATED_BY,
2231 LAST_UPDATE_LOGIN ,
2232 OBJECT_Version_number)
2233 SELECT pv_search_attr_values_s.nextval,
2234 entity_id,
2235 attribute_id,
2236 attr_value,
2237 SYSDATE,
2238 p_user_id,
2239 SYSDATE,
2240 p_user_id,
2241 p_user_id,
2242 1.0
2243 FROM (
2244 SELECT DISTINCT a.entity_id, attr_value attr_value, attribute_id
2245 FROM pv_enty_attr_values a,
2246 pv_partner_id_session b
2247 WHERE a.entity = 'PARTNER' AND
2248 a.entity_id = b.partner_id AND
2249 latest_flag = 'Y' AND
2250 attr_value IS NOT NULL AND
2251 DECODE(a.attribute_id, 3, attr_value_extn, 'Y') = 'Y' AND
2252 attribute_id IN (
2253 SELECT a.attribute_id
2254 FROM pv_entity_attrs a,
2255 pv_attributes_b b
2256 WHERE b.attribute_id <> 1 AND
2257 a.attribute_id = b.attribute_id AND
2258 a.enabled_flag = 'Y' AND
2259 b.enabled_flag = 'Y' AND
2260 (b.enable_matching_flag = 'Y' OR
2261 a.display_external_value_flag = 'Y') AND
2262 a.entity = 'PARTNER' AND
2263 a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
2264 b.return_type NOT IN ('NUMBER', 'CURRENCY')));
2265
2266 Debug(SQL%ROWCOUNT || ' rows processed.');
2267 l_total_rows := l_total_rows + SQL%ROWCOUNT;
2268
2269 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
2270 END IF;
2271
2272
2273 Debug('Total Elapsed Time (Internal: ' ||
2274 (DBMS_UTILITY.get_time - l_total_start) || ' hsec');
2275 Debug('Total Number of Rows Processed for This Operation: ' || l_total_rows);
2276 Debug('Throughput: ' ||
2277 ROUND((l_total_rows/(DBMS_UTILITY.get_time - l_total_start)) * 100, 2) ||
2278 ' rows/second');
2279
2280 EXCEPTION
2281 WHEN others THEN
2282 Debug('Exception Raised...');
2283 Debug(SQLCODE);
2284 Debug(SQLERRM);
2285 g_RETCODE := '1';
2286
2287 END Insert_Internal;
2288 -- ======================End of Insert_Internal =====================================
2289
2290
2291 --=============================================================================+
2292 --| Private Function |
2293 --| |
2294 --| Insert_External |
2295 --| |
2296 --| Parameters |
2297 --| IN |
2298 --| OUT |
2299 --| |
2300 --| |
2301 --| NOTES: |
2302 --| |
2303 --| HISTORY |
2304 --| |
2305 --==============================================================================
2306 PROCEDURE Insert_External (
2307 p_refresh_type IN VARCHAR2,
2308 p_user_id IN NUMBER
2309 )
2310 IS
2311 CURSOR c_num_cur_attributes IS
2312 SELECT a.attribute_id, a.batch_sql_text, b.name, b.return_type,
2313 b.performance_flag
2314 FROM pv_entity_attrs a,
2315 pv_attributes_vl b
2316 WHERE b.attribute_id <> 1 AND
2317 a.attribute_id = b.attribute_id AND
2318 a.enabled_flag = 'Y' AND
2319 b.enabled_flag = 'Y' AND
2320 (b.enable_matching_flag = 'Y' OR
2321 a.display_external_value_flag = 'Y') AND
2322 a.entity = 'PARTNER' AND
2323 (a.attr_data_type IN ('EXTERNAL', 'EXT_INT') OR
2324 (NVL(b.performance_flag, 'N') = 'Y')) AND
2325 b.attribute_type <> 'FUNCTION' AND
2326 b.return_type IN ('NUMBER', 'CURRENCY')
2327 ORDER BY a.attribute_id;
2328
2329
2330 CURSOR c_other_attributes IS
2331 SELECT a.attribute_id, a.batch_sql_text, b.name, b.return_type,
2332 b.performance_flag
2333 FROM pv_entity_attrs a,
2334 pv_attributes_vl b
2335 WHERE b.attribute_id <> 1 AND
2336 a.attribute_id = b.attribute_id AND
2337 a.enabled_flag = 'Y' AND
2338 b.enabled_flag = 'Y' AND
2339 (b.enable_matching_flag = 'Y' OR
2340 a.display_external_value_flag = 'Y') AND
2341 a.entity = 'PARTNER' AND
2342 (a.attr_data_type IN ('EXTERNAL', 'EXT_INT') OR
2343 NVL(b.performance_flag, 'N') = 'Y') AND
2344 b.attribute_type <> 'FUNCTION' AND
2345 b.return_type NOT IN ('NUMBER', 'CURRENCY')
2346 ORDER BY a.attribute_id;
2347
2348
2349 TYPE t_ref_cursor IS REF CURSOR;
2350 lc_currency_att_values t_ref_cursor;
2351
2352 l_do_not_process BOOLEAN := FALSE;
2353
2354 l_start NUMBER;
2355 l_start2 NUMBER;
2356 l_insert_sql VARCHAR2(4000);
2357 l_ddl_sql VARCHAR2(32000);
2358 l_batch_sql_text VARCHAR2(5000);
2359 l_partner_id NUMBER;
2360 l_last_message VARCHAR2(30000);
2361 l_new_partner_clause VARCHAR2(100) :=
2362 ' AND partner_id IN (SELECT partner_id FROM ' || g_partner_temp_table || ')';
2363
2364 BEGIN
2365 -- *****************************************************************
2366 -- *****************************************************************
2367 -- Full/Incr Refresh - External Attributes
2368 -- *****************************************************************
2369 -- *****************************************************************
2370 -- --------------------------------------------------------------------------
2371 -- Set the insert statement, which will later be appended with batch_sql_text
2372 -- to create the full insert statment.
2373 -- --------------------------------------------------------------------------
2374 l_insert_sql :=
2375 'INSERT /*+ APPEND */
2376 INTO pv_search_attr_mirror (
2377 SEARCH_ATTR_VALUES_ID,
2378 PARTY_ID,
2379 ATTRIBUTE_ID,
2380 ATTR_TEXT_DUMMY,
2381 CREATION_DATE,
2382 CREATED_BY,
2383 LAST_UPDATE_DATE,
2384 LAST_UPDATED_BY,
2385 LAST_UPDATE_LOGIN ,
2386 OBJECT_Version_number)
2387 SELECT pv_search_attr_values_s.nextval,
2388 partner_id,
2389 ATTRIBUTE_ID_DUMMY,
2390 --attr_value,
2391 SYSDATE,
2392 :p_user_id,
2393 SYSDATE,
2394 :p_user_id,
2395 :p_user_id,
2396 1.0
2397 FROM (';
2398
2399
2400 IF (p_refresh_type = g_incr_refresh) THEN
2401 l_insert_sql := REPLACE(l_insert_sql, '/*+ APPEND */', ' ');
2402 l_insert_sql := REPLACE(l_insert_sql, 'pv_search_attr_mirror (',
2403 'pv_search_attr_values (');
2404 END IF;
2405
2406 -- --------------------------------------------------------------------------
2407 -- Process NUMBER and CURRENCY return_type.
2408 -- --------------------------------------------------------------------------
2409 l_start := dbms_utility.get_time;
2410 Debug('-- **********************************************************');
2411 Debug('-- Processing External NUMBER and CURRENCY attributes...');
2412 Debug('-- **********************************************************');
2413 dbms_application_info.set_module(
2414 module_name => g_module_name,
2415 action_name => 'External - NUM/CURRENCY'
2416 );
2417
2418
2419 FOR x IN c_num_cur_attributes LOOP
2420 l_do_not_process := FALSE;
2421 -- -----------------------------------------------------------------
2422 -- Determine the attribute should be refreshed based on refresh
2423 -- frequency.
2424 -- -----------------------------------------------------------------
2425 IF (p_refresh_type <> g_incr_refresh) THEN
2426 FOR y IN (SELECT COUNT(*) cnt
2427 FROM pv_entity_attrs
2428 WHERE attribute_id = x.attribute_id AND
2429 entity = 'PARTNER' AND
2430 (last_refresh_date IS NULL OR
2431 refresh_frequency IS NULL OR
2432 refresh_frequency_uom IS NULL OR
2433 refresh_frequency_uom IS NULL OR
2434 (last_refresh_date +
2435 DECODE(refresh_frequency_uom,
2436 'HOUR', refresh_frequency/24,
2437 'DAY', refresh_frequency,
2438 'WEEK', refresh_frequency * 7,
2439 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
2440 refresh_frequency)
2441 - NVL(last_refresh_date, SYSDATE)
2442 )
2443 ) <= SYSDATE))
2444 LOOP
2445 IF (y.cnt = 0) THEN
2446 l_do_not_process := TRUE;
2447 END IF;
2448 END LOOP;
2449 END IF;
2450
2451 IF (NOT l_do_not_process) THEN
2452
2453 IF (p_refresh_type = g_incr_refresh AND x.performance_flag = 'Y') THEN
2454 -- ---------------------------------------------------------------
2455 -- Cannot use batch_sql_text to perform refresh for performance
2456 -- attrubutes in an incremental refresh.
2457 -- ---------------------------------------------------------------
2458 null;
2459
2460 ELSE
2461 l_start2 := dbms_utility.get_time;
2462
2463 IF (x.batch_sql_text IS NULL) THEN
2464 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2465 p_msg_name => 'PV_ABSENT_BATCH_SQL_TEXT',
2466 p_token1 => 'Attribute Name',
2467 p_token1_value => x.name,
2468 p_token2 => 'Attribute ID',
2469 p_token2_value => x.attribute_id);
2470
2471 ELSE
2472
2473
2474 -- ----------------------------------------------------------------
2475 -- Replace ATTRIBUTE_ID_DUMMY with the real attribute_id. Also
2476 -- append the batch_sql_text to the insert statement.
2477 -- ----------------------------------------------------------------
2478 IF (x.return_type = 'CURRENCY') THEN
2479 -- -----------------------------------------------------------------
2480 -- For performance attributes, we want to change ATTRIBUTE_ID_DUMMY
2481 -- into:
2482 -- <attribute_id>,
2483 -- attr_value attr_text,
2484 -- SUBSTR(attr_value, 1, INSTR(attr_value, '':::'') - 1) ATTR_VALUE
2485 --
2486 -- That is, we want to parse out the currency amount in the currency
2487 -- string and insert the value into attr_value column in
2488 -- pv_search_attr_values.
2489 -- -----------------------------------------------------------------
2490 IF (x.performance_flag = 'Y') THEN
2491 l_ddl_sql := REPLACE(l_insert_sql, 'partner_id', 'entity_id partner_id');
2492
2493 l_ddl_sql := REPLACE(l_ddl_sql, 'ATTRIBUTE_ID_DUMMY',
2494 x.attribute_id || ', attr_value ATTR_TEXT, ' ||
2495 'SUBSTR(attr_value, 1, INSTR(attr_value, '':::'') - 1) ATTR_VALUE');
2496
2497 -- -----------------------------------------------------------------
2498 -- For non-performance attributes, we want to change
2499 -- ATTRIBUTE_ID_DUMMY into:
2500 --
2501 -- <attribute_id>,
2502 -- attr_value attr_text,
2503 -- pv_check_match_pub.currency_conversion(
2504 -- attr_value, <g_common_currency>, 'Y') ATTR_VALUE
2505 --
2506 -- -----------------------------------------------------------------
2507 ELSE
2508 l_ddl_sql := REPLACE(l_insert_sql, 'ATTRIBUTE_ID_DUMMY',
2509 x.attribute_id || ', attr_value ATTR_TEXT, ' ||
2510 'pv_check_match_pub.currency_conversion(' ||
2511 'attr_value, ''' || g_common_currency || ''', ''Y'') ATTR_VALUE');
2512
2513 END IF;
2514
2515
2516 l_ddl_sql := REPLACE(l_ddl_sql,
2517 'ATTR_TEXT_DUMMY',
2518 'ATTR_TEXT, ATTR_VALUE');
2519
2520 -- ----------------------------------------------------------------
2521 -- Non-Currency Attributes
2522 -- ----------------------------------------------------------------
2523 ELSE
2524 IF (x.performance_flag = 'Y') THEN
2525 l_ddl_sql := REPLACE(l_insert_sql, 'partner_id', 'entity_id partner_id');
2526
2527 ELSE
2528 l_ddl_sql := l_insert_sql;
2529 END IF;
2530
2531 l_ddl_sql := REPLACE(l_ddl_sql, 'ATTRIBUTE_ID_DUMMY',
2532 x.attribute_id || ', attr_value');
2533
2534 l_ddl_sql := REPLACE(l_ddl_sql, 'ATTR_TEXT_DUMMY', 'ATTR_VALUE');
2535 END IF;
2536
2537
2538 l_batch_sql_text := x.batch_sql_text;
2539
2540
2541 IF (p_refresh_type = g_incr_refresh) THEN
2542 -- ----------------------------------------------------------------
2543 -- In an incremental refresh (new partners only refresh), make
2544 -- sure the records are not already in the search table before
2545 -- inserting the records.
2546 -- ----------------------------------------------------------------
2547 DELETE FROM pv_search_attr_values
2548 WHERE attribute_id = x.attribute_id AND
2549 party_id IN (SELECT partner_id FROM pv_partner_id_session);
2550
2551 -- -------------------------------------------------------------
2552 -- Include the new partners only clause in the batch_sql_text.
2553 -- -------------------------------------------------------------
2554 Transform_Batch_Sql(l_batch_sql_text, l_new_partner_clause);
2555 END IF;
2556
2557
2558 l_ddl_sql := l_ddl_sql || l_batch_sql_text || ')';
2559
2560 -- ----------------------------------------------------------------
2561 -- Execute the insert.
2562 -- ----------------------------------------------------------------
2563 BEGIN
2564 Debug('Processing Attribute "' || x.name || '" (Attribute ID = ' ||
2565 x.attribute_id || ')');
2566
2567 IF (x.return_type = 'CURRENCY' AND x.performance_flag = 'Y') THEN
2568 EXECUTE IMMEDIATE l_ddl_sql
2569 USING p_user_id, p_user_id, p_user_id,
2570 g_common_currency, pv_check_match_pub.g_period_set_name,
2571 x.attribute_id, 'PARTNER';
2572
2573 ELSE
2574 EXECUTE IMMEDIATE l_ddl_sql USING p_user_id, p_user_id, p_user_id,
2575 x.attribute_id, 'PARTNER';
2576 END IF;
2577
2578
2579 Debug(SQL%ROWCOUNT || ' rows processed.');
2580
2581 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
2582 Update_Timestamp (
2583 p_attribute_id => x.attribute_id,
2584 p_timestamp => SYSDATE
2585 );
2586
2587 COMMIT;
2588 END IF;
2589
2590 Debug('Elapsed Time: ' ||
2591 (DBMS_UTILITY.get_time - l_start2) || ' hsec');
2592
2593
2594 EXCEPTION
2595 WHEN FND_API.G_EXC_ERROR THEN
2596 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2597 -- -------------------------------------------------------------------
2598 -- Retrieve the last message from the message queue which
2599 -- contains the exception raised by the called program
2600 -- e.g. currency_conversion
2601 -- -------------------------------------------------------------------
2602 -- Reset the pointer to the last message of the queue
2603 fnd_msg_pub.reset(fnd_msg_pub.G_LAST);
2604
2605 -- -------------------------------------------------------------------
2606 -- Go back to the second to last message which contains the message
2607 -- raised by the called program (e.g. currency_conversion)
2608 -- -------------------------------------------------------------------
2609 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
2610 p_encoded => FND_API.g_false);
2611 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
2612 p_encoded => FND_API.g_false);
2613 Debug(l_last_message);
2614
2615 Debug('----------------------------------------------------');
2616 Debug('Attribute ID: ' || x.attribute_id);
2617 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2618
2619 g_RETCODE := '1';
2620
2621
2622 -- -------------------------------------------------------------------
2623 -- If there is an exception with curreny_conversion, we need to "roll"
2624 -- back changes. In our case, this means copy from the search table
2625 -- and insert into the mirror table.
2626 -- -------------------------------------------------------------------
2627 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
2628 INSERT /*+ APPEND */ INTO pv_search_attr_mirror
2629 (SEARCH_ATTR_VALUES_ID,
2630 PARTY_ID,
2631 SHORT_NAME,
2632 ATTR_TEXT,
2633 CREATION_DATE,
2634 CREATED_BY,
2635 LAST_UPDATE_DATE,
2636 LAST_UPDATE_LOGIN,
2637 OBJECT_VERSION_NUMBER,
2638 LAST_UPDATED_BY,
2639 SECURITY_GROUP_ID,
2640 ATTRIBUTE_ID,
2641 ATTR_VALUE
2642 )
2643 SELECT SEARCH_ATTR_VALUES_ID,
2644 PARTY_ID,
2645 SHORT_NAME,
2646 ATTR_TEXT,
2647 CREATION_DATE,
2648 CREATED_BY,
2649 LAST_UPDATE_DATE,
2650 LAST_UPDATE_LOGIN,
2651 OBJECT_VERSION_NUMBER,
2652 LAST_UPDATED_BY,
2653 SECURITY_GROUP_ID,
2654 ATTRIBUTE_ID,
2655 ATTR_VALUE
2656 FROM pv_search_attr_values
2657 WHERE attribute_id = x.attribute_id;
2658
2659 COMMIT;
2660 END IF;
2661
2662 WHEN others THEN
2663 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2664 Debug('Error executing insert statement for "' || x.name || '"');
2665 Debug('Attribute ID: ' || x.attribute_id);
2666 Debug(SQLCODE || '==>' || SQLERRM);
2667 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2668 g_RETCODE := '1';
2669
2670 -- -------------------------------------------------------------------
2671 -- If there is an exception with curreny_conversion, we need to "roll"
2672 -- back changes. In our case, this means copy from the search table
2673 -- and insert into the mirror table.
2674 -- -------------------------------------------------------------------
2675 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
2676 INSERT /*+ APPEND */ INTO pv_search_attr_mirror
2677 (SEARCH_ATTR_VALUES_ID,
2678 PARTY_ID,
2679 SHORT_NAME,
2680 ATTR_TEXT,
2681 CREATION_DATE,
2682 CREATED_BY,
2683 LAST_UPDATE_DATE,
2684 LAST_UPDATE_LOGIN,
2685 OBJECT_VERSION_NUMBER,
2686 LAST_UPDATED_BY,
2687 SECURITY_GROUP_ID,
2688 ATTRIBUTE_ID,
2689 ATTR_VALUE
2690 )
2691 SELECT SEARCH_ATTR_VALUES_ID,
2692 PARTY_ID,
2693 SHORT_NAME,
2694 ATTR_TEXT,
2695 CREATION_DATE,
2696 CREATED_BY,
2697 LAST_UPDATE_DATE,
2698 LAST_UPDATE_LOGIN,
2699 OBJECT_VERSION_NUMBER,
2700 LAST_UPDATED_BY,
2701 SECURITY_GROUP_ID,
2702 ATTRIBUTE_ID,
2703 ATTR_VALUE
2704 FROM pv_search_attr_values
2705 WHERE attribute_id = x.attribute_id;
2706
2707 COMMIT;
2708 END IF;
2709 END;
2710 END IF;
2711 END IF;
2712 END IF;
2713 END LOOP;
2714
2715
2716 Debug('Elapsed Time (NUMBER/CURRENCY): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
2717
2718 -- --------------------------------------------------------------------------
2719 -- Process return_types other than NUMBER and CURRENCY.
2720 -- --------------------------------------------------------------------------
2721 l_start := dbms_utility.get_time;
2722 Debug('___________________________________________________________');
2723 Debug('-- **********************************************************');
2724 Debug('-- Processing External OTHER attributes...');
2725 Debug('-- **********************************************************');
2726
2727 dbms_application_info.set_module(
2728 module_name => g_module_name,
2729 action_name => 'External - OTHER'
2730 );
2731
2732
2733 FOR x IN c_other_attributes LOOP
2734 l_do_not_process := FALSE;
2735 -- -----------------------------------------------------------------
2736 -- Determine the attribute should be refreshed based on refresh
2737 -- frequency.
2738 -- -----------------------------------------------------------------
2739 IF (p_refresh_type <> g_incr_refresh) THEN
2740 FOR y IN (SELECT COUNT(*) cnt
2741 FROM pv_entity_attrs
2742 WHERE attribute_id = x.attribute_id AND
2743 entity = 'PARTNER' AND
2744 (last_refresh_date IS NULL OR
2745 refresh_frequency IS NULL OR
2746 refresh_frequency_uom IS NULL OR
2747 (last_refresh_date +
2748 DECODE(refresh_frequency_uom,
2749 'HOUR', refresh_frequency/24,
2750 'DAY', refresh_frequency,
2751 'WEEK', refresh_frequency * 7,
2752 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
2753 refresh_frequency)
2754 - NVL(last_refresh_date, SYSDATE)
2755 )
2756 ) <= SYSDATE))
2757 LOOP
2758 IF (y.cnt = 0) THEN
2759 l_do_not_process := TRUE;
2760 END IF;
2761 END LOOP;
2762 END IF;
2763
2764 IF (NOT l_do_not_process) THEN
2765 IF (p_refresh_type = g_incr_refresh AND x.performance_flag = 'Y') THEN
2766 -- ---------------------------------------------------------------
2767 -- Cannot use batch_sql_text to perform refresh for performance
2768 -- attrubutes in an incremental refresh.
2769 -- ---------------------------------------------------------------
2770 null;
2771
2772 ELSE
2773
2774 l_start2 := dbms_utility.get_time;
2775
2776 IF (x.batch_sql_text IS NULL) THEN
2777 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2778 p_msg_name => 'PV_ABSENT_BATCH_SQL_TEXT',
2779 p_token1 => 'Attribute Name',
2780 p_token1_value => x.name,
2781 p_token2 => 'Attribute ID',
2782 p_token2_value => x.attribute_id);
2783
2784 ELSE
2785 -- ----------------------------------------------------------------
2786 -- Replace ATTRIBUTE_ID_DUMMY with the real attribute_id. Also
2787 -- append the batch_sql_text to the insert statement.
2788 -- ----------------------------------------------------------------
2789 l_ddl_sql := REPLACE(l_insert_sql, 'ATTRIBUTE_ID_DUMMY',
2790 x.attribute_id || ', ATTR_VALUE');
2791
2792 l_ddl_sql := REPLACE(l_ddl_sql, 'ATTR_TEXT_DUMMY', 'ATTR_TEXT');
2793
2794 l_batch_sql_text := x.batch_sql_text;
2795
2796 IF (p_refresh_type = g_incr_refresh) THEN
2797 -- ----------------------------------------------------------------
2798 -- In an incremental refresh (new partners only refresh), make
2799 -- sure the records are not already in the search table before
2800 -- inserting the records.
2801 -- ----------------------------------------------------------------
2802 DELETE FROM pv_search_attr_values
2803 WHERE attribute_id = x.attribute_id AND
2804 party_id IN (SELECT partner_id FROM pv_partner_id_session);
2805
2806 -- -------------------------------------------------------------
2807 -- Include the new partners only clause in the batch_sql_text.
2808 -- -------------------------------------------------------------
2809 Transform_Batch_Sql(l_batch_sql_text, l_new_partner_clause);
2810 END IF;
2811
2812
2813 l_ddl_sql := l_ddl_sql || l_batch_sql_text || ')';
2814
2815 -- ----------------------------------------------------------------
2816 -- Execute the insert.
2817 -- ----------------------------------------------------------------
2818 BEGIN
2819 Debug('Processing Attribute "' || x.name || '" (Attribute ID = ' ||
2820 x.attribute_id || ')');
2821
2822 EXECUTE IMMEDIATE l_ddl_sql USING p_user_id, p_user_id, p_user_id,
2823 x.attribute_id, 'PARTNER';
2824
2825 Debug(SQL%ROWCOUNT || ' rows processed.');
2826
2827 IF (p_refresh_type IN (g_full_refresh, g_incr_full_refresh)) THEN
2828 Update_Timestamp (
2829 p_attribute_id => x.attribute_id,
2830 p_timestamp => SYSDATE
2831 );
2832
2833 COMMIT;
2834 END IF;
2835
2836 Debug('Elapsed Time: ' ||
2837 (DBMS_UTILITY.get_time - l_start2) || ' hsec');
2838
2839 EXCEPTION
2840 WHEN others THEN
2841 Debug('Error executing insert statement for "' || x.name || '"');
2842 Debug('Attribute ID: ' || x.attribute_id);
2843 Debug(SQLCODE || '==>' || SQLERRM);
2844 g_RETCODE := '1';
2845 END;
2846 END IF;
2847 END IF;
2848 END IF;
2849 END LOOP;
2850
2851
2852 Debug('Elapsed Time (Other): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
2853
2854 END Insert_External;
2855 -- ======================End of Insert_External==================================
2856
2857
2858
2859 --=============================================================================+
2860 --| Private Function |
2861 --| |
2862 --| Insert_Function_Perf_Attrs |
2863 --| |
2864 --| Parameters |
2865 --| IN |
2866 --| OUT |
2867 --| |
2868 --| |
2869 --| NOTES: |
2870 --| |
2871 --| HISTORY |
2872 --| |
2873 --==============================================================================
2874 PROCEDURE Insert_Function_Perf_Attrs(
2875 p_refresh_type IN VARCHAR2,
2876 p_partner_id IN NUMBER
2877 )
2878 IS
2879 -- -----------------------------------------------------------------------
2880 -- Template cursor defined here so we can use %ROWTYPE for x.
2881 -- -----------------------------------------------------------------------
2882 CURSOR lc_template IS
2883 SELECT b.attribute_id, b.performance_flag, b.attribute_type,
2884 a.sql_text, b.name, b.return_type
2885 FROM pv_entity_attrs a,
2886 pv_attributes_vl b
2887 WHERE a.attribute_id = b.attribute_id;
2888
2889 x lc_template%ROWTYPE;
2890
2891 TYPE t_ref_cursor IS REF CURSOR;
2892 c_func_perf_attrs t_ref_cursor;
2893
2894 i NUMBER;
2895 l_start NUMBER;
2896
2897 BEGIN
2898 -- ------------------------------------------------------------------------
2899 -- The cursor for getting function and performance attributes will only
2900 -- be open once. At that time, they are fetched to a table of record.
2901 -- If there are any problem with the sql_text, the exception is caught in
2902 -- the exception block and the table of record will not have that
2903 -- particular attribute ID. This ensures that the error message associated
2904 -- with a particular sql_text will only be displayed once.
2905 --
2906 -- Note this cursor is used for function attributes (full and incremental
2907 -- refresh) and performance attributes (incremental refresh only).
2908 -- ------------------------------------------------------------------------
2909 IF (g_func_perf_attrs_tbl.COUNT = 0) THEN
2910 -- ----------------------------------------------------------------------
2911 -- Incremental refresh does not consider refresh frequency.
2912 -- ----------------------------------------------------------------------
2913 IF (p_refresh_type = g_incr_refresh) THEN
2914 OPEN c_func_perf_attrs FOR
2915 SELECT b.attribute_id, b.performance_flag, b.attribute_type,
2916 a.sql_text, b.name, b.return_type
2917 FROM pv_entity_attrs a,
2918 pv_attributes_vl b
2919 WHERE a.attribute_id = b.attribute_id AND
2920 a.entity = 'PARTNER' AND
2921 a.enabled_flag = 'Y' AND
2922 b.enabled_flag = 'Y' AND
2923 (b.enable_matching_flag = 'Y' OR
2924 a.display_external_value_flag = 'Y') AND
2925 ((b.performance_flag = 'Y' AND
2926 p_refresh_type = g_incr_refresh) OR
2927 b.attribute_type = 'FUNCTION')
2928 ORDER BY b.attribute_id;
2929
2930 -- ----------------------------------------------------------------------
2931 -- Full refresh needs to account for refresh frequency.
2932 -- ----------------------------------------------------------------------
2933 ELSE
2934 OPEN c_func_perf_attrs FOR
2935 SELECT b.attribute_id, b.performance_flag, b.attribute_type,
2936 a.sql_text, b.name, b.return_type
2937 FROM pv_entity_attrs a,
2938 pv_attributes_vl b
2939 WHERE a.attribute_id = b.attribute_id AND
2940 a.entity = 'PARTNER' AND
2941 a.enabled_flag = 'Y' AND
2942 b.enabled_flag = 'Y' AND
2943 (b.enable_matching_flag = 'Y' OR
2944 a.display_external_value_flag = 'Y') AND
2945 ((b.performance_flag = 'Y' AND
2946 p_refresh_type = g_incr_refresh) OR
2947 b.attribute_type = 'FUNCTION') AND
2948 (a.last_refresh_date IS NULL OR
2949 a.refresh_frequency IS NULL OR
2950 a.refresh_frequency_uom IS NULL OR
2951 (last_refresh_date +
2952 DECODE(refresh_frequency_uom,
2953 'HOUR', refresh_frequency/24,
2954 'DAY', refresh_frequency,
2955 'WEEK', refresh_frequency * 7,
2956 'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
2957 refresh_frequency)
2958 - NVL(last_refresh_date, SYSDATE)
2959 )
2960 ) <= SYSDATE)
2961 ORDER BY b.attribute_id;
2962 END IF;
2963
2964 LOOP
2965 FETCH c_func_perf_attrs INTO x;
2966 EXIT WHEN c_func_perf_attrs%NOTFOUND;
2967
2968 BEGIN
2969 IF (x.sql_text IS NULL OR LENGTH(x.sql_text) = 0) THEN
2970 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2971 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2972 p_msg_name => 'PV_ABSENT_SQL_TEXT',
2973 p_token1 => 'Attribute Name',
2974 p_token1_value => x.name,
2975 p_token2 => 'Attribute ID',
2976 p_token2_value => x.attribute_id);
2977 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2978
2979 g_RETCODE := '1';
2980
2981 ELSE
2982 -- -----------------------------------------------------------------
2983 -- Calling UPSERT.
2984 -- -----------------------------------------------------------------
2985 g_func_perf_attrs_tbl(x.attribute_id).performance_flag := x.performance_flag;
2986 g_func_perf_attrs_tbl(x.attribute_id).attribute_type := x.attribute_type;
2987 g_func_perf_attrs_tbl(x.attribute_id).return_type := x.return_type;
2988 g_func_perf_attrs_tbl(x.attribute_id).sql_text := x.sql_text;
2989
2990 Debug('Processing attribute (' || x.name || ') (Attribute ID=' ||
2991 x.attribute_id || ')');
2992 UPSERT_func_perf_attrs(p_refresh_type, p_partner_id, x.attribute_id);
2993
2994 END IF;
2995
2996 EXCEPTION
2997 WHEN g_e_invalid_sql THEN
2998 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
2999 Debug(SQLCODE);
3000 Debug(SQLERRM);
3001 Debug('Attribute Name: ' || x.name);
3002 Debug('Attribute ID : ' || x.attribute_id);
3003 Debug('The SQL Text for this attribute is invalid.');
3004 Debug('sql_text = ' || x.sql_text);
3005 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3006 g_RETCODE := '1';
3007
3008 -- --------------------------------------------------------------
3009 -- Don't process this attribute again.
3010 -- --------------------------------------------------------------
3011 g_func_perf_attrs_tbl.DELETE(x.attribute_id);
3012
3013 WHEN g_e_undeclared_identifier THEN
3014 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3015 Debug(SQLCODE);
3016 Debug(SQLERRM);
3017 Debug('Attribute Name: ' || x.name);
3018 Debug('Attribute ID : ' || x.attribute_id);
3019 Debug('sql_text = ' || x.sql_text);
3020 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3021 g_RETCODE := '1';
3022
3023 -- --------------------------------------------------------------
3024 -- Don't process this attribute again.
3025 -- --------------------------------------------------------------
3026 g_func_perf_attrs_tbl.DELETE(x.attribute_id);
3027
3028 WHEN g_e_invliad_column_name THEN
3029 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3030 Debug(SQLCODE);
3031 Debug(SQLERRM);
3032 Debug('Attribute Name: ' || x.name);
3033 Debug('Attribute ID : ' || x.attribute_id);
3034 Debug('The SQL has an invalid column name.');
3035 Debug('sql_text = ' || x.sql_text);
3036 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3037 g_RETCODE := '1';
3038
3039 -- --------------------------------------------------------------
3040 -- Don't process this attribute again.
3041 -- --------------------------------------------------------------
3042 g_func_perf_attrs_tbl.DELETE(x.attribute_id);
3043
3044 WHEN g_e_divisor_is_zero THEN
3045 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3046 Debug(SQLCODE);
3047 Debug(SQLERRM);
3048 Debug('Attribute Name: ' || x.name);
3049 Debug('Attribute ID : ' || x.attribute_id);
3050 Debug('sql_text = ' || x.sql_text);
3051 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3052 g_RETCODE := '1';
3053
3054 -- --------------------------------------------------------------
3055 -- Don't process this attribute again.
3056 -- --------------------------------------------------------------
3057 g_func_perf_attrs_tbl.DELETE(x.attribute_id);
3058
3059 WHEN others THEN
3060 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3061 Debug(SQLCODE);
3062 Debug(SQLERRM);
3063 Debug('Attribute Name: ' || x.name);
3064 Debug('Attribute ID : ' || x.attribute_id);
3065 Debug('There is an error with this SQL text.');
3066 Debug('sql_text = ' || x.sql_text);
3067 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3068 g_RETCODE := '1';
3069
3070 -- --------------------------------------------------------------
3071 -- Don't process this attribute again.
3072 -- --------------------------------------------------------------
3073 g_func_perf_attrs_tbl.DELETE(x.attribute_id);
3074 END;
3075 END LOOP;
3076
3077 -- ------------------------------------------------------------------------
3078 -- g_func_perf_attrs PLSQ table already been populated.
3079 -- ------------------------------------------------------------------------
3080 ELSE
3081 i := g_func_perf_attrs_tbl.FIRST;
3082
3083 WHILE (i <= g_func_perf_attrs_tbl.LAST) LOOP
3084 BEGIN
3085 -- -----------------------------------------------------------------
3086 -- Calling UPSERT.
3087 -- -----------------------------------------------------------------
3088 UPSERT_func_perf_attrs(p_refresh_type, p_partner_id, i);
3089
3090 i := g_func_perf_attrs_tbl.NEXT(i);
3091
3092 EXCEPTION
3093 WHEN g_e_divisor_is_zero THEN
3094 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3095 Debug(SQLCODE);
3096 Debug(SQLERRM);
3097 Debug('Attribute ID : ' || i);
3098 Debug('sql_text = ' || g_func_perf_attrs_tbl(i).sql_text);
3099 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3100 g_RETCODE := '1';
3101
3102 -- --------------------------------------------------------------
3103 -- Don't process this attribute again.
3104 -- --------------------------------------------------------------
3105 g_func_perf_attrs_tbl.DELETE(i);
3106
3107 -- --------------------------------------------------------------
3108 -- It's extremely important to advance the counter (i) here.
3109 -- Without doing this, this becomes an infinite loop!
3110 -- --------------------------------------------------------------
3111 i := g_func_perf_attrs_tbl.NEXT(i);
3112
3113 WHEN others THEN
3114 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3115 Debug(SQLCODE);
3116 Debug(SQLERRM);
3117 Debug('Attribute ID : ' || i);
3118 Debug('There is an error with this SQL text.');
3119 Debug('sql_text = ' || g_func_perf_attrs_tbl(i).sql_text);
3120 Debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
3121 g_RETCODE := '1';
3122
3123 -- --------------------------------------------------------------
3124 -- Don't process this attribute again.
3125 -- --------------------------------------------------------------
3126 g_func_perf_attrs_tbl.DELETE(i);
3127
3128 i := g_func_perf_attrs_tbl.NEXT(i);
3129
3130 END;
3131 END LOOP;
3132 END IF;
3133
3134 END Insert_Function_Perf_Attrs;
3135 -- =======================End of Insert_Function_Perf_Attrs====================
3136
3137
3138
3139 --=============================================================================+
3140 --| Private Function |
3141 --| |
3142 --| Upsert_Func_Perf_Attrs |
3143 --| |
3144 --| Parameters |
3145 --| IN |
3146 --| OUT |
3147 --| |
3148 --| |
3149 --| NOTES: |
3150 --| |
3151 --| HISTORY |
3152 --| |
3153 --==============================================================================
3154 PROCEDURE Upsert_Func_Perf_Attrs (
3155 p_refresh_type VARCHAR2,
3156 p_partner_id NUMBER,
3157 p_attribute_id NUMBER
3158 )
3159 IS
3160 TYPE t_ref_cursor IS REF CURSOR;
3161 c_perf_attributes t_ref_cursor;
3162
3163 l_output_tbl JTF_VARCHAR2_TABLE_4000;
3164 l_tmp_tbl JTF_VARCHAR2_TABLE_4000;
3165 l_user_id NUMBER := FND_GLOBAL.USER_ID();
3166 l_output VARCHAR2(2000);
3167 l_attr_text VARCHAR2(2000);
3168 l_attr_value NUMBER;
3169 l_last_message VARCHAR2(30000);
3170
3171 BEGIN
3172 -- ------------------------------------------------------------------------
3173 -- Function Attributes
3174 -- ------------------------------------------------------------------------
3175 IF (g_func_perf_attrs_tbl(p_attribute_id).attribute_type = 'FUNCTION') THEN
3176
3177 -- ---------------------------------------------------------------------
3178 -- Execute sql_text to retrieve attribute values.
3179 -- ---------------------------------------------------------------------
3180 EXECUTE IMMEDIATE 'BEGIN ' ||
3181 g_func_perf_attrs_tbl(p_attribute_id).sql_text ||
3182 '; END;'
3183 USING p_partner_id, OUT l_output_tbl;
3184
3185 -- ---------------------------------------------------------------------
3186 -- De-dupe l_output_tbl by "selecting" its distinct values into another
3187 -- PLSQL table.
3188 -- ---------------------------------------------------------------------
3189 SELECT CAST(MULTISET(
3190 SELECT DISTINCT column_value
3191 FROM TABLE (CAST(l_output_tbl AS JTF_VARCHAR2_TABLE_4000)))
3192 AS JTF_VARCHAR2_TABLE_4000)
3193 INTO l_tmp_tbl
3194 FROM dual;
3195
3196 l_output_tbl := l_tmp_tbl;
3197
3198
3199 -- ---------------------------------------------------------------------
3200 -- Insert records retrieved from executing the function in the sql_text.
3201 -- ---------------------------------------------------------------------
3202 FOR i IN 1..l_output_tbl.COUNT LOOP
3203 BEGIN
3204 -- ------------------------------------------------------------------
3205 -- Make sure that if the currency string is NULL (note:
3206 -- ':::USD:::20031113094020' is considered as NULL since there is no
3207 -- amount), set both l_attr_text and l_attr_value to NULL so that the
3208 -- value won't be inserted into the search table.
3209 -- ------------------------------------------------------------------
3210 IF (g_func_perf_attrs_tbl(p_attribute_id).return_type = 'CURRENCY') THEN
3211 IF (l_output_tbl(i) IS NULL OR
3212 (SUBSTR(l_output_tbl(i), 1, INSTR(l_output_tbl(i), ':::') - 1)) IS NULL)
3213 THEN
3214 l_attr_text := NULL;
3215 l_attr_value := NULL;
3216
3217 ELSE
3218 l_attr_text := l_output_tbl(i);
3219 l_attr_value := pv_check_match_pub.currency_conversion
3220 (l_output_tbl(i), g_common_currency);
3221 END IF;
3222
3223 ELSIF (g_func_perf_attrs_tbl(p_attribute_id).return_type = 'NUMBER') THEN
3224 l_attr_text := NULL;
3225 l_attr_value := TO_NUMBER(l_output_tbl(i));
3226
3227 ELSE
3228 l_attr_text := l_output_tbl(i);
3229 l_attr_value := NULL;
3230 END IF;
3231
3232 -- ------------------------------------------------------------------
3233 -- Incremental Refresh
3234 -- ------------------------------------------------------------------
3235 IF (p_refresh_type = g_incr_refresh) THEN
3236 -- ----------------------------------------------------------------
3237 -- In an incremental refresh (new partners only refresh), make
3238 -- sure the records are not already in the search table before
3239 -- inserting the records.
3240 -- ----------------------------------------------------------------
3241 DELETE FROM pv_search_attr_values
3242 WHERE attribute_id = p_attribute_id AND
3243 party_id = p_partner_id;
3244
3245
3246 IF (l_attr_text IS NOT NULL OR l_attr_value IS NOT NULL) THEN
3247 INSERT INTO pv_search_attr_values (
3248 SEARCH_ATTR_VALUES_ID,
3249 PARTY_ID,
3250 ATTRIBUTE_ID,
3251 ATTR_TEXT,
3252 ATTR_VALUE,
3253 CREATION_DATE,
3254 CREATED_BY,
3255 LAST_UPDATE_DATE,
3256 LAST_UPDATED_BY,
3257 LAST_UPDATE_LOGIN ,
3258 OBJECT_Version_number)
3259 VALUES (
3260 pv_search_attr_values_s.nextval,
3261 p_partner_id,
3262 p_attribute_id,
3263 l_attr_text,
3264 l_attr_value,
3265 SYSDATE,
3266 l_user_id,
3267 SYSDATE,
3268 l_user_id,
3269 l_user_id,
3270 1.0
3271 );
3272
3273 g_non_batch_insert_count := g_non_batch_insert_count + 1;
3274 END IF;
3275
3276 -- ------------------------------------------------------------------
3277 -- Full Refresh
3278 -- ------------------------------------------------------------------
3279 ELSE
3280 IF (l_attr_text IS NOT NULL OR l_attr_value IS NOT NULL) THEN
3281 INSERT INTO pv_search_attr_mirror (
3282 SEARCH_ATTR_VALUES_ID,
3283 PARTY_ID,
3284 ATTRIBUTE_ID,
3285 ATTR_TEXT,
3286 ATTR_VALUE,
3287 CREATION_DATE,
3288 CREATED_BY,
3289 LAST_UPDATE_DATE,
3290 LAST_UPDATED_BY,
3291 LAST_UPDATE_LOGIN ,
3292 OBJECT_Version_number)
3293 VALUES (
3294 pv_search_attr_values_s.nextval,
3295 p_partner_id,
3296 p_attribute_id,
3297 l_attr_text,
3298 l_attr_value,
3299 SYSDATE,
3300 l_user_id,
3301 SYSDATE,
3302 l_user_id,
3303 l_user_id,
3304 1.0
3305 );
3306
3307 g_non_batch_insert_count := g_non_batch_insert_count + 1;
3308 END IF;
3309 END If;
3310
3311 EXCEPTION
3312 WHEN FND_API.G_EXC_ERROR THEN
3313 -- -------------------------------------------------------------------
3314 -- Retrieve the last message from the message queue which
3315 -- contains the exception raised by the called program
3316 -- e.g. currency_conversion
3317 -- -------------------------------------------------------------------
3318 -- Reset the pointer to the last message of the queue
3319 fnd_msg_pub.reset(fnd_msg_pub.G_LAST);
3320
3321 -- -------------------------------------------------------------------
3322 -- Go back to the second to last message which contains the message
3323 -- raised by the called program (e.g. currency_conversion)
3324 -- -------------------------------------------------------------------
3325 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
3326 p_encoded => FND_API.g_false);
3327 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
3328 p_encoded => FND_API.g_false);
3329 Debug(l_last_message);
3330
3331 Debug('Attribute ID: ' || p_attribute_id);
3332 Debug('Partner ID: ' || p_partner_id);
3333 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
3334
3335 g_RETCODE := '1';
3336
3337 END;
3338 END LOOP;
3339
3340 -- ------------------------------------------------------------------------
3341 -- Performance Attributes
3342 -- ------------------------------------------------------------------------
3343 ELSE
3344 OPEN c_perf_attributes FOR g_func_perf_attrs_tbl(p_attribute_id).sql_text
3345 USING p_attribute_id, 'PARTNER', p_partner_id;
3346
3347
3348 LOOP
3349 FETCH c_perf_attributes INTO l_output;
3350 EXIT WHEN c_perf_attributes%NOTFOUND;
3351
3352 BEGIN
3353
3354 IF (g_func_perf_attrs_tbl(p_attribute_id).return_type = 'CURRENCY') THEN
3355 IF (l_output IS NULL OR
3356 (SUBSTR(l_output, 1, INSTR(l_output, ':::') - 1)) IS NULL)
3357 THEN
3358 l_attr_text := NULL;
3359 l_attr_value := NULL;
3360
3361 ELSE
3362 l_attr_text := l_output;
3363 l_attr_value := pv_check_match_pub.currency_conversion
3364 (l_output, g_common_currency);
3365 END IF;
3366
3367 ELSIF (g_func_perf_attrs_tbl(p_attribute_id).return_type = 'NUMBER') THEN
3368 l_attr_text := NULL;
3369 l_attr_value := TO_NUMBER(l_output);
3370
3371 ELSE
3372 l_attr_text := l_output;
3373 l_attr_value := NULL;
3374 END IF;
3375
3376 -- ------------------------------------------------------------------
3377 -- Incremental Refresh
3378 -- ------------------------------------------------------------------
3379 IF (p_refresh_type = g_incr_refresh) THEN
3380 -- ----------------------------------------------------------------
3381 -- In an incremental refresh (new partners only refresh), make
3382 -- sure the records are not already in the search table before
3383 -- inserting the records.
3384 -- ----------------------------------------------------------------
3385 DELETE FROM pv_search_attr_values
3386 WHERE attribute_id = p_attribute_id AND
3387 party_id = p_partner_id;
3388
3389 IF (l_attr_text IS NOT NULL OR l_attr_value IS NOT NULL) THEN
3390 INSERT INTO pv_search_attr_values (
3391 SEARCH_ATTR_VALUES_ID,
3392 PARTY_ID,
3393 ATTRIBUTE_ID,
3394 ATTR_TEXT,
3395 ATTR_VALUE,
3396 CREATION_DATE,
3397 CREATED_BY,
3398 LAST_UPDATE_DATE,
3399 LAST_UPDATED_BY,
3400 LAST_UPDATE_LOGIN ,
3401 OBJECT_Version_number)
3402 VALUES (
3403 pv_search_attr_values_s.nextval,
3404 p_partner_id,
3405 p_attribute_id,
3406 l_attr_text,
3407 l_attr_value,
3408 SYSDATE,
3409 l_user_id,
3410 SYSDATE,
3411 l_user_id,
3412 l_user_id,
3413 1.0
3414 );
3415
3416 g_non_batch_insert_count := g_non_batch_insert_count + 1;
3417 END IF;
3418
3419 -- ------------------------------------------------------------------
3420 -- Full Refresh
3421 -- ------------------------------------------------------------------
3422 ELSE
3423 Debug('Wrong Entry: the codes should never have enter this area.');
3424 Debug('Full Refresh of performance attributes does not use sql_text.');
3425 END If;
3426
3427 EXCEPTION
3428 WHEN FND_API.G_EXC_ERROR THEN
3429 -- -------------------------------------------------------------------
3430 -- Retrieve the last message from the message queue which
3431 -- contains the exception raised by the called program
3432 -- e.g. currency_conversion
3433 -- -------------------------------------------------------------------
3434 -- Reset the pointer to the last message of the queue
3435 fnd_msg_pub.reset(fnd_msg_pub.G_LAST);
3436
3437 -- -------------------------------------------------------------------
3438 -- Go back to the second to last message which contains the message
3439 -- raised by the called program (e.g. currency_conversion)
3440 -- -------------------------------------------------------------------
3441 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
3442 p_encoded => FND_API.g_false);
3443 l_last_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_PREVIOUS,
3444 p_encoded => FND_API.g_false);
3445 Debug(l_last_message);
3446
3447 Debug('Attribute ID: ' || p_attribute_id);
3448 Debug('Partner ID: ' || p_partner_id);
3449 Debug('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
3450
3451 g_RETCODE := '1';
3452
3453 END;
3454 END LOOP;
3455
3456 CLOSE c_perf_attributes;
3457 END IF;
3458
3459 END Upsert_Func_Perf_Attrs;
3460 -- =======================End of Upsert_Func_Perf_Attrs========================
3461
3462
3463
3464
3465
3466 --=============================================================================+
3467 --| Private Function |
3468 --| |
3469 --| Transform_Batch_Sql |
3470 --| |
3471 --| Parameters |
3472 --| IN |
3473 --| OUT |
3474 --| |
3475 --| |
3476 --| NOTES: This procedure is only used in the case of a INCR refresh. |
3477 --| |
3478 --| HISTORY |
3479 --| |
3480 --==============================================================================
3481 PROCEDURE Transform_Batch_Sql (
3482 p_batch_sql_text IN OUT NOCOPY VARCHAR2,
3483 p_new_partner_clause IN VARCHAR2
3484 )
3485 IS
3486 l_group_str VARCHAR2(20) := 'GROUP ';
3487 l_by_str VARCHAR2(10) := 'BY';
3488 l_group_by VARCHAR2(25);
3489
3490 BEGIN
3491 FOR i IN 1..10 LOOP
3492 l_group_by := l_group_str || l_by_str;
3493
3494 IF (INSTR(UPPER(p_batch_sql_text), l_group_by) > 0) THEN
3495 p_batch_sql_text :=
3496 REPLACE(UPPER(p_batch_sql_text), l_group_by,
3497 p_new_partner_clause || ' ' || l_group_by);
3498
3499 RETURN;
3500 END IF;
3501
3502 l_group_str := l_group_str || ' ';
3503 END LOOP;
3504
3505 p_batch_sql_text := p_batch_sql_text || ' ' || p_new_partner_clause;
3506
3507 END Transform_Batch_Sql;
3508 -- ======================End of Transform_Batch_Sql=============================
3509
3510
3511
3512
3513 --=============================================================================+
3514 --| Private Function |
3515 --| |
3516 --| Update_Timestamp |
3517 --| |
3518 --| Parameters |
3519 --| IN |
3520 --| OUT |
3521 --| |
3522 --| |
3523 --| NOTES: This procedure is only used in the case of a FULL refresh. |
3524 --| |
3525 --| HISTORY |
3526 --| |
3527 --==============================================================================
3528 PROCEDURE Update_Timestamp (
3529 p_attribute_id NUMBER,
3530 p_timestamp DATE := SYSDATE
3531 )
3532 IS
3533
3534 BEGIN
3535 UPDATE pv_entity_attrs
3536 SET last_refresh_date = p_timestamp
3537 WHERE entity = 'PARTNER' AND
3538 attribute_id = p_attribute_id;
3539
3540 END Update_Timestamp;
3541 -- ======================End of Update_Timestamp================================
3542
3543
3544
3545
3546 --=============================================================================+
3547 --| Private Function |
3548 --| |
3549 --| Disable_Drop_Indexes |
3550 --| |
3551 --| Parameters |
3552 --| IN |
3553 --| OUT |
3554 --| |
3555 --| |
3556 --| NOTES: |
3557 --| |
3558 --| HISTORY |
3559 --| |
3560 --==============================================================================
3561 PROCEDURE Disable_Drop_Indexes(
3562 p_mirror_table IN VARCHAR2,
3563 p_pv_schema_owner IN VARCHAR2
3564 )
3565 IS
3566 CURSOR c_indexes (pc_mirror_table IN VARCHAR2,
3567 pc_pv_schema_owner IN VARCHAR2,
3568 pc_index_type IN VARCHAR2)
3569 IS
3570 SELECT a.index_name
3571 FROM dba_indexes a
3572 WHERE a.table_name = pc_mirror_table AND
3573 a.table_owner = pc_pv_schema_owner AND
3574 a.uniqueness = pc_index_type AND
3575 a.owner = pc_pv_schema_owner
3576 ORDER BY a.index_name;
3577
3578 CURSOR c_pk_unique_constraints (pc_mirror_table IN VARCHAR2,
3579 pc_pv_schema_owner IN VARCHAR2)
3580 IS
3581 SELECT constraint_name
3582 FROM dba_constraints
3583 WHERE table_name = pc_mirror_table AND
3584 owner = pc_pv_schema_owner AND
3585 constraint_type IN ('P', 'U');
3586
3587 BEGIN
3588 -- ---------------------------------------------------------------------------
3589 -- Make all non-unique indexes on the mirror table unusable.
3590 -- ---------------------------------------------------------------------------
3591 FOR x IN c_indexes(p_mirror_table, p_pv_schema_owner, 'NONUNIQUE') LOOP
3592 EXECUTE IMMEDIATE
3593 'ALTER INDEX ' || p_pv_schema_owner || '.' || x.index_name || ' UNUSABLE';
3594 END LOOP;
3595
3596 -- ---------------------------------------------------------------------------
3597 -- Set SKIP_UNUSABLE_INDEXES session variable.
3598 -- ---------------------------------------------------------------------------
3599 EXECUTE IMMEDIATE
3600 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE';
3601
3602 -- ---------------------------------------------------------------------------
3603 -- On the mirror table:
3604 -- Disable all primary and unique constraints, which, in effect, drop the
3605 -- associated unique indexes.
3606 -- ---------------------------------------------------------------------------
3607 FOR x IN c_pk_unique_constraints(p_mirror_table, p_pv_schema_owner) LOOP
3608 EXECUTE IMMEDIATE
3609 'ALTER TABLE ' || p_pv_schema_owner || '.' || p_mirror_table ||
3610 ' MODIFY CONSTRAINT ' || x.constraint_name || ' DISABLE';
3611 END LOOP;
3612
3613 -- ---------------------------------------------------------------------------
3614 -- On the mirror table:
3615 -- Drop all the remaining unique indexes.
3616 -- ---------------------------------------------------------------------------
3617 FOR x IN c_indexes(p_mirror_table, p_pv_schema_owner, 'UNIQUE') LOOP
3618 EXECUTE IMMEDIATE
3619 'DROP INDEX ' || p_pv_schema_owner || '.' || x.index_name;
3620 END LOOP;
3621
3622 END Disable_Drop_Indexes;
3623 -- ======================End of Disable_Drop_Indexes============================
3624
3625
3626 --=============================================================================+
3627 --| Private Function |
3628 --| |
3629 --| Enable_Create_Indexes |
3630 --| |
3631 --| Parameters |
3632 --| IN |
3633 --| OUT |
3634 --| |
3635 --| |
3636 --| NOTES: |
3637 --| |
3638 --| HISTORY |
3639 --| |
3640 --==============================================================================
3641 PROCEDURE Enable_Create_Indexes(
3642 p_search_table IN VARCHAR2,
3643 p_mirror_table IN VARCHAR2,
3644 p_pv_schema_owner IN VARCHAR2
3645 ) IS
3646 -- ---------------------------------------------------------------------------
3647 -- Retrieve indexes for a table.
3648 -- ---------------------------------------------------------------------------
3649 CURSOR c_indexes (pc_table_name IN VARCHAR2,
3650 pc_pv_schema_owner IN VARCHAR2)
3651 IS
3652 SELECT a.index_name, a.owner, a.tablespace_name, a.pct_free,
3653 a.uniqueness
3654 FROM dba_indexes a
3655 WHERE a.table_name = pc_table_name AND
3656 a.table_owner = pc_pv_schema_owner AND
3657 a.uniqueness = 'NONUNIQUE' AND
3658 a.owner = pc_pv_schema_owner
3659 ORDER BY a.index_name;
3660
3661 -- ---------------------------------------------------------------------------
3662 -- Retrieve primary/unique constraints for a table.
3663 -- ---------------------------------------------------------------------------
3664 CURSOR c_pk_unique_constraints (pc_mirror_table IN VARCHAR2,
3665 pc_pv_schema_owner IN VARCHAR2)
3666 IS
3667 SELECT constraint_name
3668 FROM dba_constraints
3669 WHERE table_name = pc_mirror_table AND
3670 owner = pc_pv_schema_owner AND
3671 constraint_type IN ('P', 'U');
3672
3673
3674 -- ---------------------------------------------------------------------------
3675 -- Local Variables.
3676 -- ---------------------------------------------------------------------------
3677 l_index_ddl_stmt VARCHAR2(4000);
3678
3679 BEGIN
3680 -- ------------------------------------------------------------
3681 -- Rebuild nonunique indexes.
3682 -- ------------------------------------------------------------
3683 FOR x IN c_indexes(p_mirror_table, p_pv_schema_owner) LOOP
3684 l_index_ddl_stmt := 'ALTER INDEX ' || x.owner || '.' ||
3685 x.index_name || ' REBUILD NOLOGGING';
3686
3687 EXECUTE IMMEDIATE l_index_ddl_stmt;
3688 END LOOP;
3689
3690 -- ------------------------------------------------------------
3691 -- Set SKIP_UNUSABLE_INDEXES back to FALSE.
3692 -- ------------------------------------------------------------
3693 EXECUTE IMMEDIATE
3694 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = FALSE';
3695
3696 -- ------------------------------------------------------------------
3697 -- Recreate indexes. |
3698 -- ------------------
3699 -- Note that we only need to create unique indexes for they get
3700 -- dropped in the beginning of this program. However, there may be
3701 -- indexes added to the search table since the last refresh. These
3702 -- new indexes must also be present in the mirror table. To resolve
3703 -- this problem, we will recreate all the indexes on the search table
3704 -- on the mirror table. We will use the exception handler,
3705 -- g_index_columns_existed (ORA-01408) and g_name_already_used
3706 -- (ORA-00955) to take care of the indexes that already exist.
3707 --
3708 -- Since the search and mirror table exchange roles constantly, we
3709 -- need to do this process both ways by reversing the procedure
3710 -- described above.
3711 -- ------------------------------------------------------------------
3712 Create_Indexes(p_search_table, p_mirror_table, p_pv_schema_owner);
3713 Create_Indexes(p_mirror_table, p_search_table, p_pv_schema_owner);
3714
3715
3716 -- ------------------------------------------------------------
3717 -- Enable primary/unique constraints on the mirror table.
3718 -- ------------------------------------------------------------
3719 FOR x IN c_pk_unique_constraints(p_mirror_table, p_pv_schema_owner) LOOP
3720 l_index_ddl_stmt := 'ALTER TABLE ' || p_pv_schema_owner || '.' ||
3721 p_mirror_table || ' MODIFY CONSTRAINT ' ||
3722 x.constraint_name || ' ENABLE';
3723
3724 EXECUTE IMMEDIATE l_index_ddl_stmt;
3725 END LOOP;
3726 END Enable_Create_Indexes;
3727 -- ======================End of Enable_Create_Indexes============================
3728
3729
3730 --=============================================================================+
3731 --| Private Function |
3732 --| |
3733 --| Create_Indexes |
3734 --| |
3735 --| Parameters |
3736 --| IN |
3737 --| OUT |
3738 --| |
3739 --| |
3740 --| NOTES: |
3741 --| |
3742 --| HISTORY |
3743 --| |
3744 --==============================================================================
3745 PROCEDURE Create_Indexes(
3746 p_table1 IN VARCHAR2,
3747 p_table2 IN VARCHAR2,
3748 p_pv_schema_owner IN VARCHAR2
3749 )
3750 IS
3751 -- ---------------------------------------------------------------------------
3752 -- Retrieve indexes for a table.
3753 -- ---------------------------------------------------------------------------
3754 CURSOR c_indexes (pc_table_name IN VARCHAR2,
3755 pc_pv_schema_owner IN VARCHAR2)
3756 IS
3757 -- -------------------------------------------------------------------------
3758 -- The use of dbms_metadata requires invoker rights because roles are not
3759 -- enabled during the execution of a definer rights procedure.
3760 -- For this reason, we added "AUTHID CURRENT_USER" to the package
3761 -- (pvxvcons.pls). If this package is not made invoker rights enabled,
3762 -- Oracle will produce the following error when dbms_metadata.get_ddl
3763 -- is trying to extract DDL out of a non-APPS schema.
3764 --
3765 -- e.g.
3766 -- ORA-31603: object "PV_SEARCH_ATTR_VALUES_U1" of type INDEX not found
3767 -- in schema "PV"
3768 -- -------------------------------------------------------------------------
3769 SELECT index_name,
3770 dbms_metadata.get_ddl('INDEX', index_name, owner) ind_def
3771 FROM dba_indexes
3772 WHERE table_name = pc_table_name AND
3773 table_owner = pc_pv_schema_owner AND
3774 owner = pc_pv_schema_owner
3775 ORDER BY index_name;
3776
3777
3778 l_index_ddl_stmt VARCHAR2(4000);
3779
3780 BEGIN
3781 FOR x IN c_indexes(p_table1, p_pv_schema_owner) LOOP
3782 BEGIN
3783 l_index_ddl_stmt := REPLACE(x.ind_def, '"' || p_table1 || '"',
3784 '"' || p_table2 || '"');
3785
3786 IF (INSTR(l_index_ddl_stmt, '_M"') > 0) THEN
3787 l_index_ddl_stmt := REPLACE(l_index_ddl_stmt, x.index_name,
3788 SUBSTR(x.index_name, 1, LENGTH(x.index_name) - 2));
3789
3790 ELSE
3791 l_index_ddl_stmt := REPLACE(l_index_ddl_stmt, x.index_name, x.index_name || '_M');
3792 END IF;
3793
3794 EXECUTE IMMEDIATE l_index_ddl_stmt;
3795
3796 EXCEPTION
3797 -- ----------------------------------------------------------------
3798 -- If the index already exists, go to the next index.
3799 -- ----------------------------------------------------------------
3800 WHEN g_index_columns_existed THEN
3801 null;
3802
3803 WHEN g_name_already_used THEN
3804 null;
3805
3806 END;
3807 END LOOP;
3808
3809
3810 EXCEPTION
3811 WHEN g_e_definer_rights THEN
3812 Debug('Definer Rights.......................................');
3813 Debug(SQLCODE || ':::' || SQLERRM);
3814
3815
3816 END Create_Indexes;
3817 -- ==========================End of Create_Indexes===============================
3818
3819
3820
3821 --=============================================================================+
3822 --| Private Function |
3823 --| |
3824 --| Recompile_Dependencies |
3825 --| |
3826 --| Parameters |
3827 --| IN |
3828 --| OUT |
3829 --| |
3830 --| |
3831 --| NOTES: |
3832 --| |
3833 --| HISTORY |
3834 --| |
3835 --==============================================================================
3836 PROCEDURE Recompile_Dependencies(
3837 p_referenced_type IN VARCHAR2,
3838 p_referenced_name1 IN VARCHAR2,
3839 p_referenced_name2 IN VARCHAR2,
3840 p_api_package_name IN VARCHAR2
3841 )
3842 IS
3843 CURSOR c IS
3844 SELECT owner, name, type
3845 FROM dba_dependencies
3846 WHERE referenced_type = p_referenced_type AND
3847 referenced_name IN (p_referenced_name1, p_referenced_name2) AND
3848 owner = g_apps_schema;
3849
3850 l_ddl_str VARCHAR2(300);
3851 l_start NUMBER;
3852 l_start2 NUMBER;
3853
3854 BEGIN
3855 l_start := dbms_utility.get_time;
3856
3857 -- -----------------------------------------------------------------------
3858 -- Determine "APPS" schema.
3859 -- -----------------------------------------------------------------------
3860 IF (g_apps_schema IS NULL) THEN
3861 FOR x IN (SELECT user FROM dual) LOOP
3862 g_apps_schema := x.user;
3863 END LOOP;
3864 END IF;
3865
3866
3867 FOR x IN C LOOP
3868 IF (x.name <> p_api_package_name AND
3869 x.owner = g_apps_schema AND
3870 x.type = 'PACKAGE BODY')
3871 THEN
3872 FOR y IN (
3873 SELECT owner, object_name
3874 FROM dba_objects
3875 WHERE owner = x.owner AND
3876 object_name = x.name AND
3877 object_type = 'PACKAGE BODY' AND
3878 status = 'INVALID')
3879 LOOP
3880 l_start2 := dbms_utility.get_time;
3881 l_ddl_str := 'ALTER PACKAGE ' || y.owner || '.' || y.object_name ||
3882 ' COMPILE BODY';
3883 Debug(l_ddl_str);
3884
3885 BEGIN
3886 EXECUTE IMMEDIATE l_ddl_str;
3887 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start2) || ' hsec');
3888
3889 EXCEPTION
3890 WHEN OTHERS THEN
3891 Debug(SQLCODE || ':::' || SQLERRM);
3892 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start2) || ' hsec');
3893 END;
3894 END LOOP;
3895 END IF;
3896 END LOOP;
3897
3898 Debug('Elapsed Time (Recompile_Dependencies): ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
3899 END Recompile_Dependencies;
3900 -- ==========================End of Recompile_Dependencies=======================
3901
3902
3903 END PV_CONTEXT_VALUES;