[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT low_from
FROM iex_metric_ratings
WHERE score_comp_type_id = p_score_comp_type_id;
SELECT medium_from
FROM iex_metric_ratings
WHERE score_comp_type_id = p_score_comp_type_id;
SELECT high_from
FROM iex_metric_ratings
WHERE score_comp_type_id = p_score_comp_type_id;
SELECT low_to
FROM iex_metric_ratings
WHERE score_comp_type_id = p_score_comp_type_id;
SELECT medium_to
FROM iex_metric_ratings
WHERE score_comp_type_id = p_score_comp_type_id;
SELECT high_to
FROM iex_metric_ratings
WHERE score_comp_type_id = p_score_comp_type_id;
PROCEDURE UPDATE_METRIC_RATING(
p_score_comp_type_id IN NUMBER,
p_low_from IN NUMBER,
p_low_to IN NUMBER,
p_medium_from IN NUMBER,
p_medium_to IN NUMBER,
p_high_from IN NUMBER,
p_high_to IN NUMBER)
IS
CURSOR c_rating IS
SELECT '1'
FROM iex_metric_ratings
WHERE score_comp_type_id = p_score_comp_type_id;
UPDATE iex_metric_ratings
SET low_from = p_low_from, low_to = p_low_to,
medium_from = p_medium_from, medium_to = p_medium_to,
high_from = p_high_from, high_to = p_high_to,
last_update_date = SYSDATE, last_updated_by = fnd_global.user_id, last_update_login = fnd_global.login_id
WHERE score_comp_type_id = p_score_comp_type_id;
SELECT iex_metric_ratings_s.nextval
INTO l_metric_rating_id
FROM dual;
INSERT INTO iex_metric_ratings(metric_rating_id, score_comp_type_id, low_from, low_to,
medium_from, medium_to, high_from, high_to,
creation_date, created_by, last_update_date, last_updated_by, last_update_login)
VALUES (l_metric_rating_id, p_score_comp_type_id, p_low_from, p_low_to,
p_medium_from, p_medium_to, p_high_from, p_high_to,
SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id, fnd_global.user_id);
SELECT COUNT(1) FROM IEX_STRATEGY_TEMPLATES_B istl
-- WHERE istl.strategy_temp_id > 10000 and NOT EXISTS (SELECT 1 FROM IEX_STRATEGY_TEMPLATE_GROUPS istg -- bug 6067428
WHERE istl.strategy_temp_id >= 10000 and NOT EXISTS (SELECT 1 FROM IEX_STRATEGY_TEMPLATE_GROUPS istg
WHERE istg.STRATEGY_TEMP_ID = ISTL.STRATEGY_TEMP_ID);
IEX_DEBUG_PUB.logmessage('Inserting into StrategyGroups');
INSERT INTO IEX_STRATEGY_TEMPLATE_GROUPS (
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
GROUP_ID,
GROUP_NAME,
STRATEGY_RANK,
ENABLED_FLAG,
CATEGORY_TYPE,
CHANGE_STRATEGY_YN,
CHECK_LIST_YN,
CHECK_LIST_TEMP_ID,
VALID_FROM_DT,
VALID_TO_DT,
OBJECT_FILTER_ID,
STRATEGY_LEVEL,
SCORE_TOLERANCE,
STRATEGY_TEMP_ID
)
SELECT
1,
ISTL.CREATED_BY,
ISTL.CREATION_DATE,
ISTL.LAST_UPDATE_DATE,
ISTL.LAST_UPDATED_BY,
ISTL.LAST_UPDATE_LOGIN,
ISTL.REQUEST_ID,
ISTL.STRATEGY_TEMP_ID, --IEX_STRATEGY_TEMPLATE_GROUPS_S.NEXTVAL, -- bug 9256394
ISTL.STRATEGY_NAME,
ISTL.STRATEGY_RANK,
ISTL.ENABLED_FLAG,
ISTL.CATEGORY_TYPE,
ISTL.CHANGE_STRATEGY_YN,
ISTL.CHECK_LIST_YN,
ISTL.CHECK_LIST_TEMP_ID ,
ISTL.VALID_FROM_DT,
ISTL.VALID_TO_DT,
(select max(object_filter_id) from iex_object_filters where object_filter_type = 'IEXSTRAT'
and object_id = istl.strategy_temp_id), --ISTL.OBJECT_FILTER_ID,
ISTL.STRATEGY_LEVEL,
ISTL.SCORE_TOLERANCE,
ISTL.STRATEGY_TEMP_ID
FROM IEX_STRATEGY_TEMPLATES_VL ISTL
WHERE istl.strategy_temp_id >= 10000
AND (istl.strategy_temp_group_id is NULL or istl.strategy_temp_group_id = 0)
AND NOT EXISTS (SELECT 1 FROM IEX_STRATEGY_TEMPLATE_GROUPS istg
WHERE istg.STRATEGY_TEMP_ID = ISTL.STRATEGY_TEMP_ID);
UPDATE IEX_STRATEGY_TEMPLATES_b istl
SET istl.STRATEGY_TEMP_GROUP_ID =
(SELECT istg.GROUP_ID FROM IEX_STRATEGY_TEMPLATE_GROUPS istg
WHERE istg.strategy_temp_id = istl.strategy_temp_id)
WHERE istl.strategy_temp_id >= 10000
AND (istl.strategy_temp_group_id is NULL or istl.strategy_temp_group_id = 0);
select max(strategy_temp_id) into l_max_group from iex_strategy_templates_b;
select IEX_STRATEGY_TEMPLATE_GROUPS_S.nextval into l_next_seq from dual;
select IEX_STRATEGY_TEMPLATE_GROUPS_S.nextval INTO l_next_seq from dual;
PROCEDURE UPDATE_CHECKLIST_ITEM(
p_checklist_item_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR c_general_info IS
SELECT fnd_profile.value_specific('IEX_COLLECTIONS_BUCKET_NAME', -1, -1, -1, -1, -1) COLLECTIONS_BUCKET,
fnd_profile.value_specific('IEX_ENABLE_CUST_STATUS_EVENT', -1, -1, -1, -1, -1) CUST_STATUS_EVENT,
fnd_profile.value_specific('IEX_CUST_ACCESS', -1, -1, -1, -1, -1) WORK_QUEUE_ACCESS,
fnd_profile.value_specific('IEX_ACCESS_LEVEL', -1, -1, -1, -1, -1) ACCESS_LEVEL,
fnd_profile.value_specific('IEX_COLLECTIONS_RATE_TYPE', -1, -1, -1, -1, -1) RATE_TYPE
-- fnd_profile.value_specific('ACCOUNT_INTERACTION_ACTIVITY', -1, -1, -1, -1, -1) ACCOUNT_ACTIVITY,
-- fnd_profile.value_specific('IEX_DELINQUENCY_ACTIVITY', -1, -1, -1, -1, -1) DELINQUENCY_ACTIVITY,
-- fnd_profile.value_specific('DISPUTE_INTERACTION_ACTIVITY', -1, -1, -1, -1, -1) DISPUTE_ACTIVITY,
-- fnd_profile.value_specific('IEX_ADJUSTMENT_ACTIVITY', -1, -1, -1, -1, -1) ADJUSTMENT_ACTIVITY,
-- fnd_profile.value_specific('PAYMENT_INTERACTION_ACTIVITY', -1, -1, -1, -1, -1) PAYMENT_ACTIVITY,
-- fnd_profile.value_specific('PROMISE_INTERACTION_ACTIVITY', -1, -1, -1, -1, -1) PROMISE_ACTIVITY,
-- fnd_profile.value_specific('IEX_STRATEGY_ACTIVITY', -1, -1, -1, -1, -1) STRATEGY_ACTIVITY,
-- fnd_profile.value_specific('IEX_CREDIT_HOLD', -1, -1, -1, -1, -1) credit_hold,
-- fnd_profile.value_specific('IEX_SERVICE_HOLD', -1, -1, -1, -1, -1) service_hold_delin
FROM dual;
select obj.object_id,obj.object_filter_id,obj.last_updated_by,obj.last_update_login
from iex_object_filters obj, iex_strategy_template_groups stg
where stg.group_id = obj.object_id
and obj.object_filter_type = 'IEXSTRAT'
and obj.object_id > 10000
and (stg.object_filter_id is null or stg.object_filter_id = 0);
IEX_DEBUG_PUB.logmessage('Update CheckList ' || p_checklist_item_id);
update iex_strategy_template_groups
set object_filter_id=rec_object_filter.object_filter_id,last_update_date=sysdate,last_updated_by=rec_object_filter.last_updated_by,last_update_login=rec_object_filter.last_update_login
where group_id = rec_object_filter.object_id;
UPDATE iex_checklist_items_b
SET status = l_status, task_last_modified_date = SYSDATE, last_update_date = SYSDATE,
last_updated_by = G_USER_ID, last_update_login = G_LOGIN_ID
WHERE checklist_item_id = p_checklist_item_id;
UPDATE iex_checklist_items_b
SET status = l_status, task_last_modified_date = SYSDATE, last_update_date = SYSDATE,
last_updated_by = G_USER_ID, last_update_login = G_LOGIN_ID
WHERE checklist_item_id = p_checklist_item_id;
END UPDATE_CHECKLIST_ITEM;
SELECT resp.application_id, resp.responsibility_id, resp.menu_id
FROM fnd_responsibility resp, fnd_menus menu
WHERE resp.menu_id = menu.menu_id
AND menu.menu_name = 'IEX_COLLECTIONS_AGENT'
-- Begin fix bug #4930424-remove TABLE ACCESS FULL
AND resp.application_id = 695;
SELECT rf.action_id, ff.function_id
FROM fnd_resp_functions rf, fnd_form_functions ff
WHERE rf.responsibility_id(+) = p_responsibility_id
AND ff.function_name = p_function_name
AND rf.action_id(+) = ff.function_id
AND rf.rule_type(+) = 'F';
SELECT lookup_type, lookup_code, meaning,
description, enabled_flag, start_date_active, end_date_active,
territory_code, attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
tag, security_group_id, view_application_id
FROM fnd_lookup_values_vl
WHERE (lookup_type = 'IEX_HISTORY_TYPE'
AND lookup_code IN ('PAYMENT_CNSLD', 'PAYMENT_CONTRACT', 'PROMISE_CNSLD', 'PROMISE_CONTRACT'))
OR (lookup_type = 'IEX_CNSLD');
fnd_resp_functions_pkg.insert_row(x_rowid => l_rowid,
x_application_id => r_resp.application_id,
x_responsibility_id => r_resp.responsibility_id,
x_action_id => r_funct.function_id,
x_rule_type => 'F',
x_creation_date => SYSDATE,
x_created_by => 1,
x_last_updated_by => 1,
x_last_update_date => SYSDATE,
x_last_update_login => 1);
fnd_resp_functions_pkg.insert_row(x_rowid => l_rowid,
x_application_id => r_resp.application_id,
x_responsibility_id => r_resp.responsibility_id,
x_action_id => r_funct.function_id,
x_rule_type => 'F',
x_creation_date => SYSDATE,
x_created_by => 1,
x_last_updated_by => 1,
x_last_update_date => SYSDATE,
x_last_update_login => 1);
fnd_resp_functions_pkg.delete_row(x_application_id => r_resp.application_id,
x_responsibility_id => r_resp.responsibility_id,
x_action_id => r_funct.action_id,
x_rule_type => 'F');
fnd_resp_functions_pkg.delete_row(x_application_id => r_resp.application_id,
x_responsibility_id => r_resp.responsibility_id,
x_action_id => r_funct.action_id,
x_rule_type => 'F');
fnd_lookup_values_pkg.update_row(
x_lookup_type => r_lookup.lookup_type,
x_security_group_id => r_lookup.security_group_id,
x_view_application_id => r_lookup.view_application_id,
x_lookup_code => r_lookup.lookup_code,
x_tag => r_lookup.tag,
x_enabled_flag => l_enabled_flag,
x_start_date_active => r_lookup.start_date_active,
x_end_date_active => r_lookup.end_date_active,
x_territory_code => r_lookup.territory_code,
x_meaning => r_lookup.meaning,
x_description => r_lookup.description,
x_last_update_date => sysdate,
x_last_updated_by => 1,
x_last_update_login => 1,
x_attribute_category=>r_lookup.attribute_category,
x_attribute1=>r_lookup.attribute1,
x_attribute2=>r_lookup.attribute2,
x_attribute3=>r_lookup.attribute3,
x_attribute4=>r_lookup.attribute4,
x_attribute5=>r_lookup.attribute5,
x_attribute6=>r_lookup.attribute6,
x_attribute7=>r_lookup.attribute7,
x_attribute8=>r_lookup.attribute8,
x_attribute9=>r_lookup.attribute9,
x_attribute10=>r_lookup.attribute10,
x_attribute11=>r_lookup.attribute11,
x_attribute12=>r_lookup.attribute12,
x_attribute13=>r_lookup.attribute13,
x_attribute14=>r_lookup.attribute14,
x_attribute15=>r_lookup.attribute15
);
SELECT resp.application_id, resp.responsibility_id, resp.menu_id
FROM fnd_responsibility resp, fnd_menus menu
WHERE resp.menu_id = menu.menu_id
AND menu.menu_name = 'IEX_COLLECTIONS_AGENT'
-- Begin fix bug #4930424-remove TABLE ACCESS FULL
AND resp.application_id = 695;
SELECT rf.action_id, ff.function_id
FROM fnd_resp_functions rf, fnd_form_functions ff
WHERE rf.responsibility_id(+) = p_responsibility_id
AND ff.function_name = p_function_name
AND rf.action_id(+) = ff.function_id
AND rf.rule_type(+) = 'F';
Select function_id from fnd_form_functions
where function_name = 'IEX_COLL_LOAN';
Select menu_id from fnd_menus
where menu_name = 'IEX_COLL';
Select entry_sequence from fnd_menu_entries
where menu_id = p_menu_id
and function_id = p_function_id;
fnd_resp_functions_pkg.insert_row(x_rowid => l_rowid,
x_application_id => r_resp.application_id,
x_responsibility_id => r_resp.responsibility_id,
x_action_id => r_funct.function_id,
x_rule_type => 'F',
x_creation_date => SYSDATE,
x_created_by => 1,
x_last_updated_by => 1,
x_last_update_date => SYSDATE,
x_last_update_login => 1);
fnd_resp_functions_pkg.delete_row(x_application_id => r_resp.application_id,
x_responsibility_id => r_resp.responsibility_id,
x_action_id => r_funct.action_id,
x_rule_type => 'F');
FND_MENU_ENTRIES_PKG.update_row(
x_menu_id => l_menu_id,
x_entry_sequence => l_entry_sequence,
x_sub_menu_id => null,
x_function_id => l_func_id,
x_grant_flag => l_enabled_flag,
x_prompt => null,
x_description => null,
x_last_update_date => SYSDATE,
x_last_updated_by => 1,
x_last_update_login => 1 );
SELECT resp.application_id, resp.responsibility_id, resp.menu_id, resp.responsibility_key
FROM fnd_responsibility resp
WHERE resp.application_id = 695;
iex_debug_pub.logmessage('Starting Update menu for Stategy Tab based on Questionnaire ....');
update fnd_menu_entries set grant_flag = 'Y' where menu_id = 1006151 and function_id = 1011354;
update fnd_menu_entries set grant_flag = 'N' where menu_id = 1006151 and function_id = 1011354;
iex_debug_pub.logmessage('Exception ....Starting Update menu for Stategy Tab based on Questionnaire');
SELECT count(*)
INTO l_str_levels
FROM IEX_LOOKUPS_V
WHERE LOOKUP_TYPE='IEX_RUNNING_LEVEL'
AND iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
PROCEDURE UPDATE_CHECKLIST_ITEM_BY_NAME(
p_checklist_item_name IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR c_checklist IS
SELECT checklist_item_id
FROM iex_checklist_items_b
WHERE checklist_item_name = p_checklist_item_name;
update_checklist_item(l_checklist_item_id, x_return_status);
END UPDATE_CHECKLIST_ITEM_BY_NAME;
select promise_enabled, collections_methods
from iex_questionnaire_items;
PROCEDURE UPDATE_MLSETUP IS
cursor c_questionnaire_details is
select business_level, using_customer_level, using_account_level, using_billto_level,
using_delinquency_level, define_ou_running_level , define_party_running_level
from iex_questionnaire_items;
l_last_updated_by number := FND_GLOBAL.USER_ID;
l_last_update_login number := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),0);
iex_debug_pub.logmessage(' In procedure IEX_CHECKLIST_UTILITY.UPDATE_MLSETUP Begin updating multi level strategy set up in questionnaire table');
c_str_upd := 'update iex_questionnaire_items set ';
c_str_upd := c_str_upd || ' , last_update_date = sysdate , last_updated_by = ' || l_last_updated_by || ' , last_update_login = ' || l_last_update_login;
iex_debug_pub.logmessage( ' Update Statement constructed before execution ' || c_str_upd);
iex_debug_pub.logmessage('End Procedure IEX_CHECKLIST_UTILITY.UPDATE_MLSETUP updating multi level set up in questionnaire table');
End UPDATE_MLSETUP;