[Home] [Help]
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,
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,
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,
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
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,
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,
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,
1488: -- In an incremental refresh (new partners only refresh), make
1489: -- sure the records are not already in the search table before
1490: -- inserting the records.
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:
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,
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,
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,
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:
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,
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,
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;
1854: LAST_UPDATE_DATE,
1855: LAST_UPDATED_BY,
1856: LAST_UPDATE_LOGIN ,
1857: OBJECT_Version_number)
1858: SELECT pv_search_attr_values_s.nextval,
1859: entity_id,
1860: attribute_id,
1861: TO_NUMBER(attr_value),
1862: SYSDATE,
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,
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,
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,
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,
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,
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,
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,
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,
2377:
2378: IF (p_refresh_type = g_incr_refresh) THEN
2379: l_insert_sql := REPLACE(l_insert_sql, '/*+ APPEND */', ' ');
2380: l_insert_sql := REPLACE(l_insert_sql, 'pv_search_attr_mirror (',
2381: 'pv_search_attr_values (');
2382: END IF;
2383:
2384: -- --------------------------------------------------------------------------
2385: -- Process NUMBER and CURRENCY return_type.
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:
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: -- -------------------------------------------------------------
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;
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;
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: -- -------------------------------------------------------------
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:
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,
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,
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,
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
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,
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,
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