DBA Data[Home] [Help]

PACKAGE BODY: APPS.PV_CONTEXT_VALUES

Source


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