[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,
1448: LAST_UPDATED_BY,
1449: LAST_UPDATE_LOGIN ,
1450: OBJECT_Version_number
1451: )
1452: SELECT pv_search_attr_values_s.nextval,
1453: entity_id,
1454: 1,
1455: attr_value,
1456: SYSDATE,
1510: -- In an incremental refresh (new partners only refresh), make
1511: -- sure the records are not already in the search table before
1512: -- inserting the records.
1513: -- ----------------------------------------------------------------
1514: DELETE FROM pv_search_attr_values
1515: WHERE attribute_id = 1 AND
1516: party_id IN (SELECT partner_id FROM pv_partner_id_session);
1517:
1518:
1516: party_id IN (SELECT partner_id FROM pv_partner_id_session);
1517:
1518:
1519: INSERT
1520: INTO pv_search_attr_values (
1521: SEARCH_ATTR_VALUES_ID,
1522: PARTY_ID,
1523: ATTRIBUTE_ID,
1524: ATTR_TEXT,
1528: LAST_UPDATED_BY,
1529: LAST_UPDATE_LOGIN ,
1530: OBJECT_Version_number
1531: )
1532: SELECT pv_search_attr_values_s.nextval,
1533: entity_id,
1534: 1,
1535: attr_value,
1536: SYSDATE,
1676: LAST_UPDATE_DATE,
1677: LAST_UPDATED_BY,
1678: LAST_UPDATE_LOGIN ,
1679: OBJECT_Version_number)
1680: SELECT pv_search_attr_values_s.nextval,
1681: entity_id,
1682: x.attribute_id,
1683: attr_text,
1684: attr_value,
1725: -- In an incremental refresh (new partners only refresh), make
1726: -- sure the records are not already in the search table before
1727: -- inserting the records.
1728: -- ----------------------------------------------------------------
1729: DELETE FROM pv_search_attr_values
1730: WHERE attribute_id = x.attribute_id AND
1731: party_id IN (SELECT partner_id FROM pv_partner_id_session);
1732:
1733:
1731: party_id IN (SELECT partner_id FROM pv_partner_id_session);
1732:
1733:
1734: INSERT
1735: INTO pv_search_attr_values (
1736: SEARCH_ATTR_VALUES_ID,
1737: PARTY_ID,
1738: ATTRIBUTE_ID,
1739: ATTR_TEXT,
1743: LAST_UPDATE_DATE,
1744: LAST_UPDATED_BY,
1745: LAST_UPDATE_LOGIN ,
1746: OBJECT_Version_number)
1747: SELECT pv_search_attr_values_s.nextval,
1748: entity_id,
1749: x.attribute_id,
1750: attr_text,
1751: attr_value,
1835: LAST_UPDATED_BY,
1836: SECURITY_GROUP_ID,
1837: ATTRIBUTE_ID,
1838: ATTR_VALUE
1839: FROM pv_search_attr_values
1840: WHERE attribute_id = x.attribute_id;
1841:
1842: COMMIT;
1843: END IF;
1876: LAST_UPDATE_DATE,
1877: LAST_UPDATED_BY,
1878: LAST_UPDATE_LOGIN ,
1879: OBJECT_Version_number)
1880: SELECT pv_search_attr_values_s.nextval,
1881: entity_id,
1882: attribute_id,
1883: TO_NUMBER(attr_value),
1884: SYSDATE,
1995: LAST_UPDATE_DATE,
1996: LAST_UPDATED_BY,
1997: LAST_UPDATE_LOGIN ,
1998: OBJECT_Version_number)
1999: SELECT pv_search_attr_values_s.nextval,
2000: entity_id,
2001: attribute_id,
2002: attr_value,
2003: SYSDATE,
2110: -- In an incremental refresh (new partners only refresh), make
2111: -- sure the records are not already in the search table before
2112: -- inserting the records.
2113: -- ----------------------------------------------------------------
2114: DELETE FROM pv_search_attr_values
2115: WHERE party_id IN (SELECT partner_id FROM pv_partner_id_session) AND
2116: attribute_id IN (
2117: SELECT a.attribute_id
2118: FROM pv_entity_attrs a,
2128: b.return_type = 'NUMBER');
2129:
2130:
2131: INSERT
2132: INTO pv_search_attr_values (
2133: SEARCH_ATTR_VALUES_ID,
2134: PARTY_ID,
2135: ATTRIBUTE_ID,
2136: ATTR_VALUE,
2139: LAST_UPDATE_DATE,
2140: LAST_UPDATED_BY,
2141: LAST_UPDATE_LOGIN ,
2142: OBJECT_Version_number)
2143: SELECT pv_search_attr_values_s.nextval,
2144: entity_id,
2145: attribute_id,
2146: TO_NUMBER(attr_value),
2147: SYSDATE,
2194: -- In an incremental refresh (new partners only refresh), make
2195: -- sure the records are not already in the search table before
2196: -- inserting the records.
2197: -- ----------------------------------------------------------------
2198: DELETE FROM pv_search_attr_values
2199: WHERE party_id IN (SELECT partner_id FROM pv_partner_id_session) AND
2200: attribute_id IN (
2201: SELECT a.attribute_id
2202: FROM pv_entity_attrs a,
2218: -- partner type of a partner needs to be populated in the search table.
2219: -- ----------------------------------------------------------------------
2220:
2221: INSERT
2222: INTO pv_search_attr_values (
2223: SEARCH_ATTR_VALUES_ID,
2224: PARTY_ID,
2225: ATTRIBUTE_ID,
2226: ATTR_TEXT,
2229: LAST_UPDATE_DATE,
2230: LAST_UPDATED_BY,
2231: LAST_UPDATE_LOGIN ,
2232: OBJECT_Version_number)
2233: SELECT pv_search_attr_values_s.nextval,
2234: entity_id,
2235: attribute_id,
2236: attr_value,
2237: SYSDATE,
2383: LAST_UPDATE_DATE,
2384: LAST_UPDATED_BY,
2385: LAST_UPDATE_LOGIN ,
2386: OBJECT_Version_number)
2387: SELECT pv_search_attr_values_s.nextval,
2388: partner_id,
2389: ATTRIBUTE_ID_DUMMY,
2390: --attr_value,
2391: SYSDATE,
2399:
2400: IF (p_refresh_type = g_incr_refresh) THEN
2401: l_insert_sql := REPLACE(l_insert_sql, '/*+ APPEND */', ' ');
2402: l_insert_sql := REPLACE(l_insert_sql, 'pv_search_attr_mirror (',
2403: 'pv_search_attr_values (');
2404: END IF;
2405:
2406: -- --------------------------------------------------------------------------
2407: -- Process NUMBER and CURRENCY return_type.
2484: -- SUBSTR(attr_value, 1, INSTR(attr_value, '':::'') - 1) ATTR_VALUE
2485: --
2486: -- That is, we want to parse out the currency amount in the currency
2487: -- string and insert the value into attr_value column in
2488: -- pv_search_attr_values.
2489: -- -----------------------------------------------------------------
2490: IF (x.performance_flag = 'Y') THEN
2491: l_ddl_sql := REPLACE(l_insert_sql, 'partner_id', 'entity_id partner_id');
2492:
2543: -- In an incremental refresh (new partners only refresh), make
2544: -- sure the records are not already in the search table before
2545: -- inserting the records.
2546: -- ----------------------------------------------------------------
2547: DELETE FROM pv_search_attr_values
2548: WHERE attribute_id = x.attribute_id AND
2549: party_id IN (SELECT partner_id FROM pv_partner_id_session);
2550:
2551: -- -------------------------------------------------------------
2652: LAST_UPDATED_BY,
2653: SECURITY_GROUP_ID,
2654: ATTRIBUTE_ID,
2655: ATTR_VALUE
2656: FROM pv_search_attr_values
2657: WHERE attribute_id = x.attribute_id;
2658:
2659: COMMIT;
2660: END IF;
2700: LAST_UPDATED_BY,
2701: SECURITY_GROUP_ID,
2702: ATTRIBUTE_ID,
2703: ATTR_VALUE
2704: FROM pv_search_attr_values
2705: WHERE attribute_id = x.attribute_id;
2706:
2707: COMMIT;
2708: END IF;
2798: -- In an incremental refresh (new partners only refresh), make
2799: -- sure the records are not already in the search table before
2800: -- inserting the records.
2801: -- ----------------------------------------------------------------
2802: DELETE FROM pv_search_attr_values
2803: WHERE attribute_id = x.attribute_id AND
2804: party_id IN (SELECT partner_id FROM pv_partner_id_session);
2805:
2806: -- -------------------------------------------------------------
3237: -- In an incremental refresh (new partners only refresh), make
3238: -- sure the records are not already in the search table before
3239: -- inserting the records.
3240: -- ----------------------------------------------------------------
3241: DELETE FROM pv_search_attr_values
3242: WHERE attribute_id = p_attribute_id AND
3243: party_id = p_partner_id;
3244:
3245:
3243: party_id = p_partner_id;
3244:
3245:
3246: IF (l_attr_text IS NOT NULL OR l_attr_value IS NOT NULL) THEN
3247: INSERT INTO pv_search_attr_values (
3248: SEARCH_ATTR_VALUES_ID,
3249: PARTY_ID,
3250: ATTRIBUTE_ID,
3251: ATTR_TEXT,
3256: LAST_UPDATED_BY,
3257: LAST_UPDATE_LOGIN ,
3258: OBJECT_Version_number)
3259: VALUES (
3260: pv_search_attr_values_s.nextval,
3261: p_partner_id,
3262: p_attribute_id,
3263: l_attr_text,
3264: l_attr_value,
3290: LAST_UPDATED_BY,
3291: LAST_UPDATE_LOGIN ,
3292: OBJECT_Version_number)
3293: VALUES (
3294: pv_search_attr_values_s.nextval,
3295: p_partner_id,
3296: p_attribute_id,
3297: l_attr_text,
3298: l_attr_value,
3381: -- In an incremental refresh (new partners only refresh), make
3382: -- sure the records are not already in the search table before
3383: -- inserting the records.
3384: -- ----------------------------------------------------------------
3385: DELETE FROM pv_search_attr_values
3386: WHERE attribute_id = p_attribute_id AND
3387: party_id = p_partner_id;
3388:
3389: IF (l_attr_text IS NOT NULL OR l_attr_value IS NOT NULL) THEN
3386: WHERE attribute_id = p_attribute_id AND
3387: party_id = p_partner_id;
3388:
3389: IF (l_attr_text IS NOT NULL OR l_attr_value IS NOT NULL) THEN
3390: INSERT INTO pv_search_attr_values (
3391: SEARCH_ATTR_VALUES_ID,
3392: PARTY_ID,
3393: ATTRIBUTE_ID,
3394: ATTR_TEXT,
3399: LAST_UPDATED_BY,
3400: LAST_UPDATE_LOGIN ,
3401: OBJECT_Version_number)
3402: VALUES (
3403: pv_search_attr_values_s.nextval,
3404: p_partner_id,
3405: p_attribute_id,
3406: l_attr_text,
3407: l_attr_value,
3762: -- Oracle will produce the following error when dbms_metadata.get_ddl
3763: -- is trying to extract DDL out of a non-APPS schema.
3764: --
3765: -- e.g.
3766: -- ORA-31603: object "PV_SEARCH_ATTR_VALUES_U1" of type INDEX not found
3767: -- in schema "PV"
3768: -- -------------------------------------------------------------------------
3769: SELECT index_name,
3770: dbms_metadata.get_ddl('INDEX', index_name, owner) ind_def