[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_batch_insert_count NUMBER;
g_non_batch_insert_count NUMBER;
PROCEDURE Insert_Functional_Expertise(
p_refresh_type IN VARCHAR2,
p_user_id IN NUMBER
);
PROCEDURE Insert_Internal(
p_refresh_type IN VARCHAR2,
p_user_id IN NUMBER
);
PROCEDURE Insert_External(
p_refresh_type IN VARCHAR2,
p_user_id IN NUMBER
);
PROCEDURE Insert_Function_Perf_Attrs(
p_refresh_type IN VARCHAR2,
p_partner_id IN NUMBER
);
PROCEDURE Update_Timestamp (
p_attribute_id IN NUMBER,
p_timestamp IN DATE := SYSDATE
);
SELECT synonym_name, table_name
FROM dba_synonyms
WHERE owner = g_apps_schema AND
synonym_name IN ('PV_SEARCH_ATTR_VALUES', 'PV_SEARCH_ATTR_MIRROR');
SELECT i.tablespace,
i.index_tablespace,
u.oracle_username
FROM fnd_product_installations i,
fnd_application a,
fnd_oracle_userid u
WHERE a.application_short_name = 'PV' AND
a.application_id = i.application_id AND
u.oracle_id = i.oracle_id;
SELECT COUNT(*) count
FROM user_synonyms
WHERE synonym_name = pc_synonym_name;
SELECT COUNT(*) count
FROM pv_entity_attrs b,
pv_attributes_vl a
WHERE a.attribute_id = 1 AND
a.attribute_id = b.attribute_id AND
b.entity = 'PARTNER' AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
a.enable_matching_flag = 'Y' AND
(b.last_refresh_date IS NULL OR
b.refresh_frequency IS NULL OR
b.refresh_frequency_uom IS NULL OR
(b.last_refresh_date +
DECODE(refresh_frequency_uom,
'HOUR', refresh_frequency/24,
'DAY', refresh_frequency,
'WEEK', refresh_frequency * 7,
'MONTH', ADD_MONTHS(TRUNC(NVL(b.last_refresh_date, SYSDATE), 'MM'),
b.refresh_frequency)
- NVL(b.last_refresh_date, SYSDATE)
)
) <= SYSDATE);
g_batch_insert_count := 0;
g_non_batch_insert_count := 0;
FOR x IN (SELECT user FROM dual) LOOP
g_apps_schema := x.user;
FOR x IN (SELECT COUNT(*) count
FROM v$session
WHERE module LIKE 'Refresh Attribute Texts Concurrent Program%')
LOOP
IF (x.count > 0) THEN
Debug('There is already a Refresh Attribute Text CC session running.');
FOR x IN (SELECT profile_option_value
FROM fnd_profile_options a,
fnd_profile_option_values b
WHERE a.application_id = 691 AND
a.profile_option_id = b.profile_option_id AND
b.level_id = 10001 AND -- site level
b.level_value = 0 AND
a.profile_optioN_name = 'PV_REFRESH_ATTRS_LAST_UPDATE')
LOOP
l_last_incr_refresh_str := x.profile_option_value;
Debug('system since the last refresh date will be retrieved and updated.');
Debug('Update last refresh date...');
FND_PROFILE.PUT('PV_REFRESH_ATTRS_LAST_UPDATE', l_incr_timestamp);
l_ret_val := FND_PROFILE.SAVE('PV_REFRESH_ATTRS_LAST_UPDATE',
l_incr_timestamp,
'SITE');
INSERT /*+ APPEND */ INTO pv_search_attr_mirror
(SEARCH_ATTR_VALUES_ID,
PARTY_ID,
SHORT_NAME,
ATTR_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LAST_UPDATED_BY,
SECURITY_GROUP_ID,
ATTRIBUTE_ID,
ATTR_VALUE
)
SELECT SEARCH_ATTR_VALUES_ID,
PARTY_ID,
SHORT_NAME,
ATTR_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LAST_UPDATED_BY,
SECURITY_GROUP_ID,
ATTRIBUTE_ID,
ATTR_VALUE
FROM pv_search_attr_values
WHERE attribute_id IN (
SELECT a.attribute_id
FROM pv_attributes_b a,
pv_entity_attrs b
WHERE a.attribute_id = b.attribute_id AND
b.entity = 'PARTNER' AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(a.enable_matching_flag = 'Y' OR
b.display_external_value_flag = 'Y') AND
(b.last_refresh_date +
DECODE(refresh_frequency_uom,
'HOUR', refresh_frequency/24,
'DAY', refresh_frequency,
'WEEK', refresh_frequency * 7,
'MONTH', ADD_MONTHS(TRUNC(NVL(b.last_refresh_date, SYSDATE), 'MM'),
b.refresh_frequency)
- NVL(b.last_refresh_date, SYSDATE)
)
) > SYSDATE);
Debug(SQL%ROWCOUNT || ' rows inserted.');
Insert_Functional_Expertise(l_refresh_type, l_user_id);
Insert_Internal(l_refresh_type, l_user_id);
FOR x IN (SELECT COUNT(*) count
FROM pv_entity_attrs a,
pv_attributes_vl b
WHERE a.attribute_id = b.attribute_id AND
a.entity = 'PARTNER' AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(b.enable_matching_flag = 'Y' OR
a.display_external_value_flag = 'Y') AND
((b.performance_flag = 'Y' AND
l_refresh_type = g_incr_refresh) OR
b.attribute_type = 'FUNCTION') AND
(a.last_refresh_date IS NULL OR
a.refresh_frequency IS NULL OR
a.refresh_frequency_uom IS NULL OR
(last_refresh_date +
DECODE(refresh_frequency_uom,
'HOUR', refresh_frequency/24,
'DAY', refresh_frequency,
'WEEK', refresh_frequency * 7,
'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
refresh_frequency)
- NVL(last_refresh_date, SYSDATE)
)
) <= SYSDATE) )
LOOP
l_num_temp := x.count;
SELECT partner_id
FROM pv_partner_id_session;
SELECT partner_id
FROM pv_partner_profiles
WHERE status = 'A' AND
--sales_partner_flag = 'Y'
partner_resource_id IS NOT NULL;
Insert_Function_Perf_Attrs(l_refresh_type, l_partner_id);
FOR x IN (SELECT a.attribute_id
FROM pv_entity_attrs a,
pv_attributes_vl b
WHERE a.attribute_id = b.attribute_id AND
a.entity = 'PARTNER' AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(b.enable_matching_flag = 'Y' OR
a.display_external_value_flag = 'Y') AND
((b.performance_flag = 'Y' AND
l_refresh_type = g_incr_refresh) OR
b.attribute_type = 'FUNCTION') AND
(a.last_refresh_date IS NULL OR
a.refresh_frequency IS NULL OR
a.refresh_frequency_uom IS NULL OR
(last_refresh_date +
DECODE(refresh_frequency_uom,
'HOUR', refresh_frequency/24,
'DAY', refresh_frequency,
'WEEK', refresh_frequency * 7,
'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
refresh_frequency)
- NVL(last_refresh_date, SYSDATE)
)
) <= SYSDATE) )
LOOP
Update_Timestamp (
p_attribute_id => x.attribute_id,
p_timestamp => SYSDATE
);
Debug('Total Number of Rows Inserted for this operation: ' || g_non_batch_insert_count);
Insert_External(l_refresh_type, l_user_id);
Debug('Update last refresh date...');
FND_PROFILE.PUT('PV_REFRESH_ATTRS_LAST_UPDATE', l_incr_timestamp);
l_ret_val := FND_PROFILE.SAVE('PV_REFRESH_ATTRS_LAST_UPDATE',
l_incr_timestamp,
'SITE');
SELECT synonym_name, table_name
FROM dba_synonyms
WHERE owner = g_apps_schema AND
synonym_name IN (p_synonym_name, p_mirror_synonym_name);
SELECT i.tablespace,
i.index_tablespace,
u.oracle_username
FROM fnd_product_installations i,
fnd_application a,
fnd_oracle_userid u
WHERE a.application_short_name = 'PV' AND
a.application_id = i.application_id AND
u.oracle_id = i.oracle_id;
SELECT COUNT(*) count
FROM user_synonyms
WHERE synonym_name = pc_synonym_name;
select ev.table_name
from dba_editioning_views ev
where ev.owner = pc_owner and
ev.view_name = replace(pc_view_name, '$', '#');
FOR x IN (SELECT user FROM dual) LOOP
g_apps_schema := x.user;
'SELECT partner_id
FROM pv_partner_profiles pvpp
WHERE pvpp.status = ''A'' AND
partner_resource_id IS NOT NULL AND
creation_date >= :last_incr_refresh ';
Debug('Insert the list of new partners into the temporary table.............');
'INSERT INTO ' || p_partner_id_temp_table || ' ' ||
l_partner_sql
USING TO_DATE(NVL(p_last_incr_refresh_str, '12-31-1900 00:00:01'),
'MM-DD-YYYY HH24:MI:SS');
For x IN (SELECT a.partner_id, c.party_name
FROM pv_partner_id_session a,
pv_partner_profiles b,
hz_parties c
WHERE a.partner_id = b.partner_id AND
b.partner_party_id = c.party_id)
LOOP
Debug(LPAD(TO_CHAR(x.partner_id), 12) || ' ' || x.party_name);
SELECT value
FROM v$parameter
WHERE name = 'db_block_size';
PROCEDURE Insert_Functional_Expertise (
p_refresh_type IN VARCHAR2,
p_user_id IN NUMBER
)
IS
l_insert_header VARCHAR2(200);
l_insert_body VARCHAR2(1000);
INSERT /*+ APPEND */
INTO pv_search_attr_mirror (
SEARCH_ATTR_VALUES_ID,
PARTY_ID,
ATTRIBUTE_ID,
ATTR_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
OBJECT_Version_number
)
SELECT pv_search_attr_values_s.nextval,
entity_id,
1,
attr_value,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id,
1.0
FROM (
SELECT DISTINCT
a.entity_id,
DENORM.child_id attr_value
FROM pv_enty_attr_values a,
pv_entity_attrs b,
eni_prod_denorm_hrchy_v DENORM
WHERE b.attribute_id = 1 AND
a.latest_flag = 'Y' AND
a.entity = 'PARTNER' AND
a.attr_value = TO_CHAR(DENORM.parent_id) AND
a.attribute_id = b.attribute_id AND
b.entity = 'PARTNER' AND
(b.last_refresh_date IS NULL OR
b.refresh_frequency IS NULL OR
b.refresh_frequency_uom IS NULL OR
(b.last_refresh_date +
DECODE(b.refresh_frequency_uom,
'HOUR', b.refresh_frequency/24,
'DAY', b.refresh_frequency,
'WEEK', b.refresh_frequency * 7,
'MONTH', ADD_MONTHS(TRUNC(NVL(b.last_refresh_date, SYSDATE), 'MM'),
b.refresh_frequency)
- NVL(b.last_refresh_date, SYSDATE)
)
) <= SYSDATE)
);
Debug(SQL%ROWCOUNT || ' rows inserted.');
Update_Timestamp (
p_attribute_id => 1,
p_timestamp => SYSDATE
);
DELETE FROM pv_search_attr_values
WHERE attribute_id = 1 AND
party_id IN (SELECT partner_id FROM pv_partner_id_session);
INSERT
INTO pv_search_attr_values (
SEARCH_ATTR_VALUES_ID,
PARTY_ID,
ATTRIBUTE_ID,
ATTR_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
OBJECT_Version_number
)
SELECT pv_search_attr_values_s.nextval,
entity_id,
1,
attr_value,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id,
1.0
FROM (
SELECT DISTINCT
a.entity_id,
DENORM.child_id attr_value
FROM pv_enty_attr_values a,
pv_partner_id_session b,
eni_prod_denorm_hrchy_v DENORM
WHERE a.attribute_id = 1 AND
a.latest_flag = 'Y' AND
a.entity = 'PARTNER' AND
a.entity_id = b.partner_id AND
a.attr_value = TO_CHAR(DENORM.parent_id));
Debug(SQL%ROWCOUNT || ' rows inserted.');
Debug('Exception raised while inserting for "functional expertise" ' ||
'(Attribute ID = 1)');
END Insert_Functional_Expertise;
PROCEDURE Insert_Internal (
p_refresh_type IN VARCHAR2,
p_user_id IN NUMBER
)
IS
-- ----------------------------------------------------------------------
-- Local Variables
-- ----------------------------------------------------------------------
l_last_message VARCHAR2(30000);
SELECT a.attribute_id, b.name
FROM pv_entity_attrs a,
pv_attributes_vl b
WHERE b.attribute_id <> 1 AND
a.attribute_id = b.attribute_id AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(b.enable_matching_flag = 'Y' OR
a.display_external_value_flag = 'Y') AND
a.entity = 'PARTNER' AND
a.attr_data_type IN ('INTERNAL', 'INT_EXT')AND
(a.last_refresh_date IS NULL OR
a.refresh_frequency IS NULL OR
a.refresh_frequency_uom IS NULL OR
(last_refresh_date +
DECODE(refresh_frequency_uom,
'HOUR', refresh_frequency/24,
'DAY', refresh_frequency,
'WEEK', refresh_frequency * 7,
'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
refresh_frequency)
- NVL(last_refresh_date, SYSDATE)
)
) <= SYSDATE) AND
b.return_type = 'CURRENCY'
ORDER BY a.attribute_id;
INSERT /*+ APPEND */
INTO pv_search_attr_mirror (
SEARCH_ATTR_VALUES_ID,
PARTY_ID,
ATTRIBUTE_ID,
ATTR_TEXT,
ATTR_VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
OBJECT_Version_number)
SELECT pv_search_attr_values_s.nextval,
entity_id,
x.attribute_id,
attr_text,
attr_value,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id,
1.0
FROM (
SELECT DISTINCT a.entity_id,
a.attr_value attr_text,
pv_check_match_pub.Currency_Conversion(
a.attr_value, g_common_currency) attr_value
FROM pv_enty_attr_values a,
pv_partner_profiles PV
WHERE a.entity = 'PARTNER' AND
a.latest_flag = 'Y' AND
a.entity_id = PV.partner_id AND
PV.partner_resource_id IS NOT NULL AND
PV.status = 'A' AND
a.attr_value IS NOT NULL AND
a.attribute_id = x.attribute_id);
Update_Timestamp (
p_attribute_id => x.attribute_id,
p_timestamp => SYSDATE
);
DELETE FROM pv_search_attr_values
WHERE attribute_id = x.attribute_id AND
party_id IN (SELECT partner_id FROM pv_partner_id_session);
INSERT
INTO pv_search_attr_values (
SEARCH_ATTR_VALUES_ID,
PARTY_ID,
ATTRIBUTE_ID,
ATTR_TEXT,
ATTR_VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
OBJECT_Version_number)
SELECT pv_search_attr_values_s.nextval,
entity_id,
x.attribute_id,
attr_text,
attr_value,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id,
1.0
FROM (
SELECT DISTINCT a.entity_id,
a.attr_value attr_text,
pv_check_match_pub.Currency_Conversion(
a.attr_value, g_common_currency) attr_value
FROM pv_enty_attr_values a,
pv_partner_id_session b
WHERE a.entity = 'PARTNER' AND
a.latest_flag = 'Y' AND
a.attr_value IS NOT NULL AND
a.entity_id = b.partner_id AND
a.attribute_id = x.attribute_id);
INSERT /*+ APPEND */ INTO pv_search_attr_mirror
(SEARCH_ATTR_VALUES_ID,
PARTY_ID,
SHORT_NAME,
ATTR_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LAST_UPDATED_BY,
SECURITY_GROUP_ID,
ATTRIBUTE_ID,
ATTR_VALUE
)
SELECT SEARCH_ATTR_VALUES_ID,
PARTY_ID,
SHORT_NAME,
ATTR_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LAST_UPDATED_BY,
SECURITY_GROUP_ID,
ATTRIBUTE_ID,
ATTR_VALUE
FROM pv_search_attr_values
WHERE attribute_id = x.attribute_id;
INSERT /*+ APPEND */
INTO pv_search_attr_mirror (
SEARCH_ATTR_VALUES_ID,
PARTY_ID,
ATTRIBUTE_ID,
ATTR_VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
OBJECT_Version_number)
SELECT pv_search_attr_values_s.nextval,
entity_id,
attribute_id,
TO_NUMBER(attr_value),
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id,
1.0
FROM (
SELECT DISTINCT a.entity_id, a.attr_value attr_value, a.attribute_id
FROM pv_enty_attr_values a,
pv_partner_profiles PV
WHERE a.entity = 'PARTNER' AND
a.latest_flag = 'Y' AND
a.attr_value IS NOT NULL AND
a.entity_id = PV.partner_id AND
PV.partner_resource_id IS NOT NULL AND
PV.status = 'A' AND
a.attribute_id IN (
SELECT a.attribute_id
FROM pv_entity_attrs a,
pv_attributes_b b
WHERE b.attribute_id <> 1 AND
a.attribute_id = b.attribute_id AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
b.enable_matching_flag = 'Y' AND
a.entity = 'PARTNER' AND
a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
(a.last_refresh_date IS NULL OR
a.refresh_frequency IS NULL OR
a.refresh_frequency_uom IS NULL OR
(last_refresh_date +
DECODE(refresh_frequency_uom,
'HOUR', refresh_frequency/24,
'DAY', refresh_frequency,
'WEEK', refresh_frequency * 7,
'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
refresh_frequency)
- NVL(last_refresh_date, SYSDATE)
)
) <= SYSDATE) AND
b.return_type = 'NUMBER'));
FOR x IN (SELECT a.attribute_id
FROM pv_entity_attrs a,
pv_attributes_b b
WHERE b.attribute_id <> 1 AND
a.attribute_id = b.attribute_id AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(b.enable_matching_flag = 'Y' OR
a.display_external_value_flag = 'Y') AND
a.entity = 'PARTNER' AND
a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
(a.last_refresh_date IS NULL OR
a.refresh_frequency IS NULL OR
a.refresh_frequency_uom IS NULL OR
(last_refresh_date +
DECODE(refresh_frequency_uom,
'HOUR', refresh_frequency/24,
'DAY', refresh_frequency,
'WEEK', refresh_frequency * 7,
'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
refresh_frequency)
- NVL(last_refresh_date, SYSDATE)
)
) <= SYSDATE) AND
b.return_type = 'NUMBER')
LOOP
Update_Timestamp (
p_attribute_id => x.attribute_id,
p_timestamp => SYSDATE
);
INSERT /*+ APPEND */
INTO pv_search_attr_mirror (
SEARCH_ATTR_VALUES_ID,
PARTY_ID,
ATTRIBUTE_ID,
ATTR_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
OBJECT_Version_number)
SELECT pv_search_attr_values_s.nextval,
entity_id,
attribute_id,
attr_value,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id,
1.0
FROM (
SELECT DISTINCT a.entity_id, a.attr_value attr_value, a.attribute_id
FROM pv_enty_attr_values a,
pv_partner_profiles PV
WHERE a.entity = 'PARTNER' AND
a.latest_flag = 'Y' AND
a.attr_value IS NOT NULL AND
DECODE(a.attribute_id, 3, attr_value_extn, 'Y') = 'Y' AND
a.entity_id = PV.partner_id AND
PV.partner_resource_id IS NOT NULL AND
PV.status = 'A' AND
a.attribute_id IN (
SELECT a.attribute_id
FROM pv_entity_attrs a,
pv_attributes_b b
WHERE b.attribute_id <> 1 AND
a.attribute_id = b.attribute_id AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(b.enable_matching_flag = 'Y' OR
a.display_external_value_flag = 'Y') AND
a.entity = 'PARTNER' AND
a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
(a.last_refresh_date IS NULL OR
a.refresh_frequency IS NULL OR
a.refresh_frequency_uom IS NULL OR
(last_refresh_date +
DECODE(refresh_frequency_uom,
'HOUR', refresh_frequency/24,
'DAY', refresh_frequency,
'WEEK', refresh_frequency * 7,
'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
refresh_frequency)
- NVL(last_refresh_date, SYSDATE)
)
) <= SYSDATE) AND
b.return_type NOT IN ('NUMBER', 'CURRENCY')));
FOR x IN (SELECT a.attribute_id
FROM pv_entity_attrs a,
pv_attributes_b b
WHERE b.attribute_id <> 1 AND
a.attribute_id = b.attribute_id AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(b.enable_matching_flag = 'Y' OR
a.display_external_value_flag = 'Y') AND
a.entity = 'PARTNER' AND
a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
(a.last_refresh_date IS NULL OR
a.refresh_frequency IS NULL OR
a.refresh_frequency_uom IS NULL OR
(last_refresh_date +
DECODE(refresh_frequency_uom,
'HOUR', refresh_frequency/24,
'DAY', refresh_frequency,
'WEEK', refresh_frequency * 7,
'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
refresh_frequency)
- NVL(last_refresh_date, SYSDATE)
)
) <= SYSDATE) AND
b.return_type NOT IN ('NUMBER', 'CURRENCY'))
LOOP
Update_Timestamp (
p_attribute_id => x.attribute_id,
p_timestamp => SYSDATE
);
DELETE FROM pv_search_attr_values
WHERE party_id IN (SELECT partner_id FROM pv_partner_id_session) AND
attribute_id IN (
SELECT a.attribute_id
FROM pv_entity_attrs a,
pv_attributes_b b
WHERE b.attribute_id <> 1 AND
a.attribute_id = b.attribute_id AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(b.enable_matching_flag = 'Y' OR
a.display_external_value_flag = 'Y') AND
a.entity = 'PARTNER' AND
a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
b.return_type = 'NUMBER');
INSERT
INTO pv_search_attr_values (
SEARCH_ATTR_VALUES_ID,
PARTY_ID,
ATTRIBUTE_ID,
ATTR_VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
OBJECT_Version_number)
SELECT pv_search_attr_values_s.nextval,
entity_id,
attribute_id,
TO_NUMBER(attr_value),
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id,
1.0
FROM (
SELECT DISTINCT a.entity_id, attr_value attr_value, attribute_id
FROM pv_enty_attr_values a,
pv_partner_id_session b
WHERE a.entity = 'PARTNER' AND
a.entity_id = b.partner_id AND
latest_flag = 'Y' AND
attr_value IS NOT NULL AND
attribute_id IN (
SELECT a.attribute_id
FROM pv_entity_attrs a,
pv_attributes_b b
WHERE b.attribute_id <> 1 AND
a.attribute_id = b.attribute_id AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(b.enable_matching_flag = 'Y' OR
a.display_external_value_flag = 'Y') AND
a.entity = 'PARTNER' AND
a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
b.return_type = 'NUMBER'));
DELETE FROM pv_search_attr_values
WHERE party_id IN (SELECT partner_id FROM pv_partner_id_session) AND
attribute_id IN (
SELECT a.attribute_id
FROM pv_entity_attrs a,
pv_attributes_b b
WHERE b.attribute_id <> 1 AND
a.attribute_id = b.attribute_id AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(b.enable_matching_flag = 'Y' OR
a.display_external_value_flag = 'Y') AND
a.entity = 'PARTNER' AND
a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
b.return_type NOT IN ('NUMBER', 'CURRENCY'));
INSERT
INTO pv_search_attr_values (
SEARCH_ATTR_VALUES_ID,
PARTY_ID,
ATTRIBUTE_ID,
ATTR_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
OBJECT_Version_number)
SELECT pv_search_attr_values_s.nextval,
entity_id,
attribute_id,
attr_value,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id,
1.0
FROM (
SELECT DISTINCT a.entity_id, attr_value attr_value, attribute_id
FROM pv_enty_attr_values a,
pv_partner_id_session b
WHERE a.entity = 'PARTNER' AND
a.entity_id = b.partner_id AND
latest_flag = 'Y' AND
attr_value IS NOT NULL AND
DECODE(a.attribute_id, 3, attr_value_extn, 'Y') = 'Y' AND
attribute_id IN (
SELECT a.attribute_id
FROM pv_entity_attrs a,
pv_attributes_b b
WHERE b.attribute_id <> 1 AND
a.attribute_id = b.attribute_id AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(b.enable_matching_flag = 'Y' OR
a.display_external_value_flag = 'Y') AND
a.entity = 'PARTNER' AND
a.attr_data_type IN ('INTERNAL', 'INT_EXT') AND
b.return_type NOT IN ('NUMBER', 'CURRENCY')));
END Insert_Internal;
PROCEDURE Insert_External (
p_refresh_type IN VARCHAR2,
p_user_id IN NUMBER
)
IS
CURSOR c_num_cur_attributes IS
SELECT a.attribute_id, a.batch_sql_text, b.name, b.return_type,
b.performance_flag
FROM pv_entity_attrs a,
pv_attributes_vl b
WHERE b.attribute_id <> 1 AND
a.attribute_id = b.attribute_id AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(b.enable_matching_flag = 'Y' OR
a.display_external_value_flag = 'Y') AND
a.entity = 'PARTNER' AND
(a.attr_data_type IN ('EXTERNAL', 'EXT_INT') OR
(NVL(b.performance_flag, 'N') = 'Y')) AND
b.attribute_type <> 'FUNCTION' AND
b.return_type IN ('NUMBER', 'CURRENCY')
ORDER BY a.attribute_id;
SELECT a.attribute_id, a.batch_sql_text, b.name, b.return_type,
b.performance_flag
FROM pv_entity_attrs a,
pv_attributes_vl b
WHERE b.attribute_id <> 1 AND
a.attribute_id = b.attribute_id AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(b.enable_matching_flag = 'Y' OR
a.display_external_value_flag = 'Y') AND
a.entity = 'PARTNER' AND
(a.attr_data_type IN ('EXTERNAL', 'EXT_INT') OR
NVL(b.performance_flag, 'N') = 'Y') AND
b.attribute_type <> 'FUNCTION' AND
b.return_type NOT IN ('NUMBER', 'CURRENCY')
ORDER BY a.attribute_id;
l_insert_sql VARCHAR2(4000);
' AND partner_id IN (SELECT partner_id FROM ' || g_partner_temp_table || ')';
l_insert_sql :=
'INSERT /*+ APPEND */
INTO pv_search_attr_mirror (
SEARCH_ATTR_VALUES_ID,
PARTY_ID,
ATTRIBUTE_ID,
ATTR_TEXT_DUMMY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
OBJECT_Version_number)
SELECT pv_search_attr_values_s.nextval,
partner_id,
ATTRIBUTE_ID_DUMMY,
--attr_value,
SYSDATE,
:p_user_id,
SYSDATE,
:p_user_id,
:p_user_id,
1.0
FROM (';
l_insert_sql := REPLACE(l_insert_sql, '/*+ APPEND */', ' ');
l_insert_sql := REPLACE(l_insert_sql, 'pv_search_attr_mirror (',
'pv_search_attr_values (');
FOR y IN (SELECT COUNT(*) cnt
FROM pv_entity_attrs
WHERE attribute_id = x.attribute_id AND
entity = 'PARTNER' AND
(last_refresh_date IS NULL OR
refresh_frequency IS NULL OR
refresh_frequency_uom IS NULL OR
refresh_frequency_uom IS NULL OR
(last_refresh_date +
DECODE(refresh_frequency_uom,
'HOUR', refresh_frequency/24,
'DAY', refresh_frequency,
'WEEK', refresh_frequency * 7,
'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
refresh_frequency)
- NVL(last_refresh_date, SYSDATE)
)
) <= SYSDATE))
LOOP
IF (y.cnt = 0) THEN
l_do_not_process := TRUE;
l_ddl_sql := REPLACE(l_insert_sql, 'partner_id', 'entity_id partner_id');
l_ddl_sql := REPLACE(l_insert_sql, 'ATTRIBUTE_ID_DUMMY',
x.attribute_id || ', attr_value ATTR_TEXT, ' ||
'pv_check_match_pub.currency_conversion(' ||
'attr_value, ''' || g_common_currency || ''', ''Y'') ATTR_VALUE');
l_ddl_sql := REPLACE(l_insert_sql, 'partner_id', 'entity_id partner_id');
l_ddl_sql := l_insert_sql;
DELETE FROM pv_search_attr_values
WHERE attribute_id = x.attribute_id AND
party_id IN (SELECT partner_id FROM pv_partner_id_session);
Update_Timestamp (
p_attribute_id => x.attribute_id,
p_timestamp => SYSDATE
);
INSERT /*+ APPEND */ INTO pv_search_attr_mirror
(SEARCH_ATTR_VALUES_ID,
PARTY_ID,
SHORT_NAME,
ATTR_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LAST_UPDATED_BY,
SECURITY_GROUP_ID,
ATTRIBUTE_ID,
ATTR_VALUE
)
SELECT SEARCH_ATTR_VALUES_ID,
PARTY_ID,
SHORT_NAME,
ATTR_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LAST_UPDATED_BY,
SECURITY_GROUP_ID,
ATTRIBUTE_ID,
ATTR_VALUE
FROM pv_search_attr_values
WHERE attribute_id = x.attribute_id;
Debug('Error executing insert statement for "' || x.name || '"');
INSERT /*+ APPEND */ INTO pv_search_attr_mirror
(SEARCH_ATTR_VALUES_ID,
PARTY_ID,
SHORT_NAME,
ATTR_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LAST_UPDATED_BY,
SECURITY_GROUP_ID,
ATTRIBUTE_ID,
ATTR_VALUE
)
SELECT SEARCH_ATTR_VALUES_ID,
PARTY_ID,
SHORT_NAME,
ATTR_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LAST_UPDATED_BY,
SECURITY_GROUP_ID,
ATTRIBUTE_ID,
ATTR_VALUE
FROM pv_search_attr_values
WHERE attribute_id = x.attribute_id;
FOR y IN (SELECT COUNT(*) cnt
FROM pv_entity_attrs
WHERE attribute_id = x.attribute_id AND
entity = 'PARTNER' AND
(last_refresh_date IS NULL OR
refresh_frequency IS NULL OR
refresh_frequency_uom IS NULL OR
(last_refresh_date +
DECODE(refresh_frequency_uom,
'HOUR', refresh_frequency/24,
'DAY', refresh_frequency,
'WEEK', refresh_frequency * 7,
'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
refresh_frequency)
- NVL(last_refresh_date, SYSDATE)
)
) <= SYSDATE))
LOOP
IF (y.cnt = 0) THEN
l_do_not_process := TRUE;
l_ddl_sql := REPLACE(l_insert_sql, 'ATTRIBUTE_ID_DUMMY',
x.attribute_id || ', ATTR_VALUE');
DELETE FROM pv_search_attr_values
WHERE attribute_id = x.attribute_id AND
party_id IN (SELECT partner_id FROM pv_partner_id_session);
Update_Timestamp (
p_attribute_id => x.attribute_id,
p_timestamp => SYSDATE
);
Debug('Error executing insert statement for "' || x.name || '"');
END Insert_External;
PROCEDURE Insert_Function_Perf_Attrs(
p_refresh_type IN VARCHAR2,
p_partner_id IN NUMBER
)
IS
-- -----------------------------------------------------------------------
-- Template cursor defined here so we can use %ROWTYPE for x.
-- -----------------------------------------------------------------------
CURSOR lc_template IS
SELECT b.attribute_id, b.performance_flag, b.attribute_type,
a.sql_text, b.name, b.return_type
FROM pv_entity_attrs a,
pv_attributes_vl b
WHERE a.attribute_id = b.attribute_id;
SELECT b.attribute_id, b.performance_flag, b.attribute_type,
a.sql_text, b.name, b.return_type
FROM pv_entity_attrs a,
pv_attributes_vl b
WHERE a.attribute_id = b.attribute_id AND
a.entity = 'PARTNER' AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(b.enable_matching_flag = 'Y' OR
a.display_external_value_flag = 'Y') AND
((b.performance_flag = 'Y' AND
p_refresh_type = g_incr_refresh) OR
b.attribute_type = 'FUNCTION')
ORDER BY b.attribute_id;
SELECT b.attribute_id, b.performance_flag, b.attribute_type,
a.sql_text, b.name, b.return_type
FROM pv_entity_attrs a,
pv_attributes_vl b
WHERE a.attribute_id = b.attribute_id AND
a.entity = 'PARTNER' AND
a.enabled_flag = 'Y' AND
b.enabled_flag = 'Y' AND
(b.enable_matching_flag = 'Y' OR
a.display_external_value_flag = 'Y') AND
((b.performance_flag = 'Y' AND
p_refresh_type = g_incr_refresh) OR
b.attribute_type = 'FUNCTION') AND
(a.last_refresh_date IS NULL OR
a.refresh_frequency IS NULL OR
a.refresh_frequency_uom IS NULL OR
(last_refresh_date +
DECODE(refresh_frequency_uom,
'HOUR', refresh_frequency/24,
'DAY', refresh_frequency,
'WEEK', refresh_frequency * 7,
'MONTH', ADD_MONTHS(TRUNC(NVL(last_refresh_date, SYSDATE), 'MM'),
refresh_frequency)
- NVL(last_refresh_date, SYSDATE)
)
) <= SYSDATE)
ORDER BY b.attribute_id;
g_func_perf_attrs_tbl.DELETE(x.attribute_id);
g_func_perf_attrs_tbl.DELETE(x.attribute_id);
g_func_perf_attrs_tbl.DELETE(x.attribute_id);
g_func_perf_attrs_tbl.DELETE(x.attribute_id);
g_func_perf_attrs_tbl.DELETE(x.attribute_id);
g_func_perf_attrs_tbl.DELETE(i);
g_func_perf_attrs_tbl.DELETE(i);
END Insert_Function_Perf_Attrs;
SELECT CAST(MULTISET(
SELECT DISTINCT column_value
FROM TABLE (CAST(l_output_tbl AS JTF_VARCHAR2_TABLE_4000)))
AS JTF_VARCHAR2_TABLE_4000)
INTO l_tmp_tbl
FROM dual;
DELETE FROM pv_search_attr_values
WHERE attribute_id = p_attribute_id AND
party_id = p_partner_id;
INSERT INTO pv_search_attr_values (
SEARCH_ATTR_VALUES_ID,
PARTY_ID,
ATTRIBUTE_ID,
ATTR_TEXT,
ATTR_VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
OBJECT_Version_number)
VALUES (
pv_search_attr_values_s.nextval,
p_partner_id,
p_attribute_id,
l_attr_text,
l_attr_value,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_user_id,
1.0
);
g_non_batch_insert_count := g_non_batch_insert_count + 1;
INSERT INTO pv_search_attr_mirror (
SEARCH_ATTR_VALUES_ID,
PARTY_ID,
ATTRIBUTE_ID,
ATTR_TEXT,
ATTR_VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
OBJECT_Version_number)
VALUES (
pv_search_attr_values_s.nextval,
p_partner_id,
p_attribute_id,
l_attr_text,
l_attr_value,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_user_id,
1.0
);
g_non_batch_insert_count := g_non_batch_insert_count + 1;
DELETE FROM pv_search_attr_values
WHERE attribute_id = p_attribute_id AND
party_id = p_partner_id;
INSERT INTO pv_search_attr_values (
SEARCH_ATTR_VALUES_ID,
PARTY_ID,
ATTRIBUTE_ID,
ATTR_TEXT,
ATTR_VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
OBJECT_Version_number)
VALUES (
pv_search_attr_values_s.nextval,
p_partner_id,
p_attribute_id,
l_attr_text,
l_attr_value,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_user_id,
1.0
);
g_non_batch_insert_count := g_non_batch_insert_count + 1;
PROCEDURE Update_Timestamp (
p_attribute_id NUMBER,
p_timestamp DATE := SYSDATE
)
IS
BEGIN
UPDATE pv_entity_attrs
SET last_refresh_date = p_timestamp
WHERE entity = 'PARTNER' AND
attribute_id = p_attribute_id;
END Update_Timestamp;
SELECT a.index_name
FROM dba_indexes a
WHERE a.table_name = pc_mirror_table AND
a.table_owner = pc_pv_schema_owner AND
a.uniqueness = pc_index_type AND
a.owner = pc_pv_schema_owner
ORDER BY a.index_name;
SELECT constraint_name
FROM dba_constraints
WHERE table_name = pc_mirror_table AND
owner = pc_pv_schema_owner AND
constraint_type IN ('P', 'U');
SELECT a.index_name, a.owner, a.tablespace_name, a.pct_free,
a.uniqueness
FROM dba_indexes a
WHERE a.table_name = pc_table_name AND
a.table_owner = pc_pv_schema_owner AND
a.uniqueness = 'NONUNIQUE' AND
a.owner = pc_pv_schema_owner
ORDER BY a.index_name;
SELECT constraint_name
FROM dba_constraints
WHERE table_name = pc_mirror_table AND
owner = pc_pv_schema_owner AND
constraint_type IN ('P', 'U');
SELECT index_name,
dbms_metadata.get_ddl('INDEX', index_name, owner) ind_def
FROM dba_indexes
WHERE table_name = pc_table_name AND
table_owner = pc_pv_schema_owner AND
owner = pc_pv_schema_owner
ORDER BY index_name;
SELECT owner, name, type
FROM dba_dependencies
WHERE referenced_type = p_referenced_type AND
referenced_name IN (p_referenced_name1, p_referenced_name2) AND
owner = g_apps_schema;
FOR x IN (SELECT user FROM dual) LOOP
g_apps_schema := x.user;
SELECT owner, object_name
FROM dba_objects
WHERE owner = x.owner AND
object_name = x.name AND
object_type = 'PACKAGE BODY' AND
status = 'INVALID')
LOOP
l_start2 := dbms_utility.get_time;