[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT (*)
INTO l_tmp_exist
FROM cn_lookups
WHERE lookup_type = 'PAYMENT_GROUP_CODE' AND lookup_code = p_payment_group_code;
SELECT lookup_code
INTO x_quota_type_code
FROM cn_lookups
WHERE lookup_type = 'QUOTA_TYPE' AND UPPER (meaning) = UPPER (l_element_type);
SELECT lookup_code
INTO x_incentive_type_code
FROM cn_lookups
WHERE lookup_type = 'INCENTIVE_TYPE' AND UPPER (meaning) = UPPER (l_incentive_type);
SELECT DECODE (p_payee_assign_flag, fnd_api.g_miss_char, 'No', NULL, 'No', LTRIM (RTRIM (p_payee_assign_flag)))
INTO l_flag
FROM SYS.DUAL;
SELECT lookup_code
INTO x_payee_assign_flag
FROM fnd_lookups
WHERE lookup_type = 'YES_NO' AND UPPER (meaning) = UPPER (l_flag);
SELECT DECODE (p_vesting_flag, fnd_api.g_miss_char, 'No', NULL, 'No', LTRIM (RTRIM (p_vesting_flag)))
INTO l_flag
FROM SYS.DUAL;
SELECT lookup_code
INTO x_vesting_flag
FROM fnd_lookups
WHERE lookup_type = 'YES_NO' AND UPPER (meaning) = UPPER (l_flag);
SELECT DECODE (p_addup_from_rev_class_flag, fnd_api.g_miss_char, 'No', NULL, 'No', LTRIM (RTRIM (p_addup_from_rev_class_flag)))
INTO l_flag
FROM SYS.DUAL;
SELECT lookup_code
INTO x_addup_from_rev_class_flag
FROM fnd_lookups
WHERE lookup_type = 'YES_NO' AND UPPER (meaning) = UPPER (l_flag);
SELECT DECODE (p_rt_sched_custom_flag, fnd_api.g_miss_char, 'No', NULL, 'No', LTRIM (RTRIM (p_rt_sched_custom_flag)))
INTO l_flag
FROM SYS.DUAL;
SELECT lookup_code
INTO x_rt_sched_custom_flag
FROM fnd_lookups
WHERE lookup_type = 'YES_NO' AND UPPER (meaning) = UPPER (l_flag);
SELECT quota_id
INTO l_pe_rec.quota_id
FROM cn_quotas_v
WHERE NAME = l_pe_rec.NAME
and org_id = l_pe_rec.ORG_ID;
SELECT calc_formula_id
INTO l_pe_rec.calc_formula_id
FROM cn_calc_formulas
WHERE NAME = l_pe_rec.calc_formula_name
and org_id = l_pe_rec.ORG_ID;
SELECT credit_type_id
INTO l_pe_rec.credit_type_id
FROM cn_credit_types
WHERE NAME = l_pe_rec.credit_type and org_id = p_plan_element_rec.org_id and rownum=1;
SELECT interval_type_id
INTO l_pe_rec.interval_type_id
FROM cn_interval_types
WHERE NAME = l_pe_rec.interval_name
and org_id = l_pe_rec.ORG_ID;
SELECT DECODE (p_plan_element_rec.target, fnd_api.g_miss_num, 0, NULL, 0, p_plan_element_rec.target)
INTO l_pe_rec.target
FROM SYS.DUAL;
SELECT DECODE (p_plan_element_rec.payment_amount, fnd_api.g_miss_num, 0, NULL, 0, p_plan_element_rec.payment_amount)
INTO l_pe_rec.payment_amount
FROM SYS.DUAL;
SELECT DECODE (p_plan_element_rec.performance_goal, fnd_api.g_miss_num, 0, NULL, 0, p_plan_element_rec.performance_goal)
INTO l_pe_rec.performance_goal
FROM SYS.DUAL;
SELECT NAME,
description,
quota_type_code,
calc_formula_id,
target
FROM cn_quotas_v
WHERE NAME = p_pe_rec.NAME;
SELECT calc_sql_exp_id,
DBMS_LOB.SUBSTR (piped_sql_select) sql_select,
DBMS_LOB.SUBSTR (piped_expression_disp) expr_disp
FROM cn_calc_sql_exps
WHERE '|' || DBMS_LOB.SUBSTR (piped_sql_select) LIKE '%|(' || p_quota_id || 'PE.%';
l_ss_end := INSTR (e.sql_select, '|', l_ss_start + 1);
l_ss_seg := SUBSTR (e.sql_select, l_ss_start, l_ss_end - l_ss_start);
UPDATE cn_calc_sql_exps
SET expression_disp = l_new_expr_disp,
piped_expression_disp = l_new_pexpr_disp
WHERE calc_sql_exp_id = e.calc_sql_exp_id;
SELECT COUNT (*)
INTO l_tmp_exist
FROM cn_lookups
WHERE lookup_type = 'QUOTA_TYPE' AND lookup_code = p_pe_rec.quota_type_code;
SELECT COUNT (*)
INTO l_tmp_exist
FROM cn_lookups
WHERE lookup_type = 'INCENTIVE_TYPE' AND lookup_code = p_pe_rec.incentive_type_code;
PROCEDURE update_rate_quotas (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type,
p_pe_rec_old IN cn_chk_plan_element_pkg.pe_rec_type,
p_rt_quota_asgns_rec_tbl IN rt_quota_asgns_rec_tbl_type := g_miss_rt_quota_asgns_rec_tbl,
p_quota_name IN VARCHAR2,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Rate_Quotas';
cn_rt_quota_asgns_pkg.DELETE_RECORD (x_quota_id => p_pe_rec_old.quota_id, x_calc_formula_id => NULL, x_rt_quota_asgn_id => NULL);
cn_rt_quota_asgns_pkg.INSERT_RECORD (x_quota_id => p_pe_rec.quota_id, x_calc_formula_id => p_pe_rec.calc_formula_id);
cn_rt_quota_asgns_pvt.update_rt_quota_asgns (p_api_version => p_api_version,
p_init_msg_list => 'T',
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => p_quota_name,
p_org_id => p_pe_rec.org_id,
p_rt_quota_asgns_rec_tbl => p_rt_quota_asgns_rec_tbl,
x_loading_status => x_loading_status,
x_object_version_number => l_object_version_number
);
END update_rate_quotas;
PROCEDURE insert_rate_quotas (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type,
p_rt_quota_asgns_rec_tbl IN rt_quota_asgns_rec_tbl_type := g_miss_rt_quota_asgns_rec_tbl,
p_quota_name IN VARCHAR2,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Insert_Rate_Quotas';
cn_rt_quota_asgns_pkg.INSERT_RECORD (x_quota_id => p_pe_rec.quota_id, x_calc_formula_id => p_pe_rec.calc_formula_id);
END insert_rate_quotas;
PROCEDURE update_period_quotas (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type,
p_pe_rec_old IN cn_chk_plan_element_pkg.pe_rec_type,
p_period_quotas_rec_tbl IN period_quotas_rec_tbl_type := g_miss_period_quotas_rec_tbl,
p_quota_name IN VARCHAR2,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_tmp NUMBER;
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Period_quotas';
cn_period_quotas_pkg.DELETE_RECORD (p_pe_rec_old.quota_id);
cn_period_quotas_grp.update_period_quotas (p_api_version => p_api_version,
p_init_msg_list => 'T',
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => p_quota_name,
p_period_quotas_rec_tbl => p_period_quotas_rec_tbl,
x_loading_status => x_loading_status
);
cn_period_quotas_grp.update_period_quotas (p_api_version => p_api_version,
p_init_msg_list => 'T',
p_commit => p_commit,
p_validation_level =>p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => p_quota_name,
p_period_quotas_rec_tbl =>p_period_quotas_rec_tbl,
x_loading_status => x_loading_status
);
END update_period_quotas;
UPDATE cn_trx_factors
SET event_factor = p_trx_factor_rec_tbl (OUTER).event_factor
WHERE quota_rule_id = l_quota_rule_id AND quota_id = p_quota_id AND trx_type = p_trx_factor_rec_tbl (INNER).trx_type;
IF (x_return_status <> fnd_api.g_ret_sts_success) OR x_loading_status NOT IN ('CN_UPDATED', 'CN_INSERTED')
THEN
RAISE fnd_api.g_exc_error;
SELECT q.quota_id
FROM cn_quotas_v q
WHERE q.NAME = pe_name;
PROCEDURE check_valid_update (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_quota_name_old IN VARCHAR2,
p_new_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec,
x_old_pe_rec OUT NOCOPY cn_chk_plan_element_pkg.pe_rec_type,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Check_Valid_Update';
SELECT q.quota_id,
q.calc_formula_id,
cn_chk_plan_element_pkg.get_calc_formula_name (q.calc_formula_id),
-- clku, 5/9/2002
q.quota_type_code,
q.start_date,
q.end_date
FROM cn_quotas_v q
WHERE q.NAME = pe_name;
END check_valid_update;
g_last_update_date DATE := SYSDATE;
g_last_updated_by NUMBER := fnd_global.user_id;
g_last_update_login NUMBER := fnd_global.login_id;
x_loading_status := 'CN_INSERTED';
cn_quotas_pkg.begin_record (x_operation => 'INSERT',
x_org_id => l_pe_rec.org_id,
x_object_version_number => l_pe_rec.object_version_number,
x_rowid => g_rowid,
x_indirect_credit => l_pe_rec.indirect_credit,
x_quota_id => l_pe_rec.quota_id,
x_name => l_pe_rec.NAME,
x_target => l_pe_rec.target,
x_quota_type_code => l_pe_rec.quota_type_code,
x_usage_code => NULL,
x_payment_amount => l_pe_rec.payment_amount,
x_description => l_pe_rec.description,
x_start_date => l_pe_rec.start_date,
x_end_date => l_pe_rec.end_date,
x_quota_status => l_pe_rec.quota_status,
x_calc_formula_id => l_pe_rec.calc_formula_id,
x_incentive_type_code => l_pe_rec.incentive_type_code,
x_credit_type_id => l_pe_rec.credit_type_id,
x_rt_sched_custom_flag => l_pe_rec.rt_sched_custom_flag,
x_package_name => l_pe_rec.package_name,
x_performance_goal => l_pe_rec.performance_goal,
x_interval_type_id => l_pe_rec.interval_type_id,
x_payee_assign_flag => l_pe_rec.payee_assign_flag,
x_vesting_flag => l_pe_rec.vesting_flag,
x_expense_account_id => l_p_plan_element_rec.expense_account_id,
x_liability_account_id => l_p_plan_element_rec.liability_account_id,
x_quota_group_code => l_p_plan_element_rec.quota_group_code, --clku PAYMENT ENHANCEMENT,
x_payment_group_code => l_p_plan_element_rec.payment_group_code,
x_quota_unspecified => NULL,
x_last_update_date => g_last_update_date,
x_last_updated_by => g_last_updated_by,
x_creation_date => g_creation_date,
x_created_by => g_created_by,
x_last_update_login => g_last_update_login,
x_program_type => g_program_type,
x_period_type_code => NULL,
x_start_num => NULL,
x_end_num => NULL,
x_addup_from_rev_class_flag => l_pe_rec.addup_from_rev_class_flag
--clku, bug 2854576
,
x_attribute_category => l_p_plan_element_rec.attribute_category,
x_attribute1 => l_p_plan_element_rec.attribute1,
x_attribute2 => l_p_plan_element_rec.attribute2,
x_attribute3 => l_p_plan_element_rec.attribute3,
x_attribute4 => l_p_plan_element_rec.attribute4,
x_attribute5 => l_p_plan_element_rec.attribute5,
x_attribute6 => l_p_plan_element_rec.attribute6,
x_attribute7 => l_p_plan_element_rec.attribute7,
x_attribute8 => l_p_plan_element_rec.attribute8,
x_attribute9 => l_p_plan_element_rec.attribute9,
x_attribute10 => l_p_plan_element_rec.attribute10,
x_attribute11 => l_p_plan_element_rec.attribute11,
x_attribute12 => l_p_plan_element_rec.attribute12,
x_attribute13 => l_p_plan_element_rec.attribute13,
x_attribute14 => l_p_plan_element_rec.attribute14,
x_attribute15 => l_p_plan_element_rec.attribute15,
x_salesrep_end_flag => l_p_plan_element_rec.sreps_enddated_flag
);
x_loading_status := 'CN_INSERTED';
insert_rate_quotas (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pe_rec => l_pe_rec,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
p_quota_name => l_p_plan_element_rec.NAME,
p_loading_status => x_loading_status,
x_loading_status => l_loading_status
);
x_loading_status := 'CN_INSERTED';
x_loading_status := 'CN_INSERTED';
x_loading_status := 'CN_INSERTED';
x_loading_status := 'CN_INSERTED';
insert_rate_quotas (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pe_rec => l_pe_rec,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
p_quota_name => l_p_plan_element_rec.NAME,
p_loading_status => x_loading_status,
x_loading_status => l_loading_status
);
x_loading_status := 'CN_INSERTED';
x_loading_status := 'CN_INSERTED';
IF (x_loading_status = 'CN_INSERTED')
THEN
-- Check the Table count is > 0 then Call the Group Package with
-- table record and the Quota Type.
IF l_p_revenue_class_rec_tbl.COUNT > 0
THEN
-- call the group api to insert the quota rules and the trx factors.
cn_quota_rules_grp.create_quota_rules (p_api_version => p_api_version,
p_init_msg_list => 'T',
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => l_p_plan_element_rec.NAME,
p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
x_loading_status => x_loading_status
);
END IF; -- end if x_loading_status = 'CN_INSERTED'
ELSIF l_p_rev_uplift_rec_tbl.COUNT > 0 AND x_loading_status = 'CN_INSERTED'
THEN
-- call the group API to create the quota rule uplifts
FOR i IN l_p_rev_uplift_rec_tbl.FIRST .. l_p_rev_uplift_rec_tbl.LAST LOOP
l_p_rev_uplift_rec_tbl1(i).org_id := l_p_rev_uplift_rec_tbl(i).org_id;
ELSIF (x_loading_status <> 'CN_INSERTED')
THEN
RAISE fnd_api.g_exc_error;
p_action_code => 'I', /* I - Insert */
p_bind_data_id => l_bind_data_id,
p_oai_param => NULL,
p_oai_array => l_oai_array,
x_return_code => x_return_status
);
PROCEDURE update_plan_element (
p_api_version IN NUMBER := 0,
p_init_msg_list IN VARCHAR2 := cn_api.g_false,
p_commit IN VARCHAR2 := cn_api.g_false,
p_validation_level IN NUMBER := cn_api.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_new_plan_element_rec IN plan_element_rec_type := g_miss_plan_element_rec,
p_quota_name_old IN VARCHAR2,
p_revenue_class_rec_tbl IN revenue_class_rec_tbl_type := g_miss_revenue_class_rec_tbl,
p_rev_uplift_rec_tbl IN rev_uplift_rec_tbl_type := g_miss_rev_uplift_rec_tbl,
p_trx_factor_rec_tbl IN trx_factor_rec_tbl_type := g_miss_trx_factor_rec_tbl,
p_period_quotas_rec_tbl IN period_quotas_rec_tbl_type := g_miss_period_quotas_rec_tbl,
p_rt_quota_asgns_rec_tbl IN rt_quota_asgns_rec_tbl_type := g_miss_rt_quota_asgns_rec_tbl,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Plan_Element';
g_last_update_date DATE := SYSDATE;
g_last_updated_by NUMBER := fnd_global.user_id;
g_last_update_login NUMBER := fnd_global.login_id;
SELECT srp_period_quota_id,org_id
FROM cn_srp_period_quotas
WHERE quota_id = pe_quota_id;
SELECT ccf.number_dim
FROM cn_quotas_v cq,
cn_calc_formulas ccf
WHERE cq.quota_id = l_quota_id AND cq.calc_formula_id = ccf.calc_formula_id;
SAVEPOINT update_plan_element;
x_loading_status := 'CN_UPDATED';
'cn.plsql.cn_plan_element_pub.update_plan_element.org_validate',
'Validated org_id = ' || l_org_id || ' status = '||l_status);
IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'UPDATE_PLAN_ELEMENT', 'B', 'C')
THEN
cn_plan_element_cuhk.update_plan_element_pre (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_new_plan_element_rec => l_p_new_plan_element_rec,
p_quota_name_old => l_p_quota_name_old,
p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
x_loading_status => x_loading_status
);
IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'UPDATE_PLAN_ELEMENT', 'B', 'V')
THEN
cn_plan_element_vuhk.update_plan_element_pre (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_new_plan_element_rec => l_p_new_plan_element_rec,
p_quota_name_old => l_p_quota_name_old,
p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
x_loading_status => x_loading_status
);
x_loading_status := 'CN_UPDATED';
check_valid_update (x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name_old => l_p_quota_name_old,
p_new_pe_rec => l_pe_rec,
x_old_pe_rec => l_pe_rec_old,
p_loading_status => x_loading_status,
x_loading_status => l_loading_status
);
ELSIF (x_loading_status = 'CN_UPDATED')
THEN
l_pe_rec.quota_id := l_pe_rec_old.quota_id;
cn_quotas_pkg.begin_record (x_operation => 'UPDATE',
x_org_id => l_pe_rec.org_id,
x_object_version_number => l_pe_rec.object_version_number,
x_indirect_credit => l_pe_rec.indirect_credit,
x_rowid => g_rowid,
x_quota_id => l_pe_rec_old.quota_id,
x_name => l_pe_rec.NAME,
x_target => l_pe_rec.target,
x_quota_type_code => l_pe_rec.quota_type_code,
x_usage_code => NULL,
x_payment_amount => l_pe_rec.payment_amount,
x_description => l_pe_rec.description,
x_start_date => l_pe_rec.start_date,
x_end_date => l_pe_rec.end_date,
x_quota_status => l_pe_rec.quota_status,
x_calc_formula_id => l_pe_rec.calc_formula_id,
x_incentive_type_code => l_pe_rec.incentive_type_code,
x_credit_type_id => l_pe_rec.credit_type_id,
x_rt_sched_custom_flag => l_pe_rec.rt_sched_custom_flag,
x_package_name => l_pe_rec.package_name,
x_performance_goal => l_pe_rec.performance_goal,
x_interval_type_id => l_pe_rec.interval_type_id,
x_payee_assign_flag => l_pe_rec.payee_assign_flag,
x_vesting_flag => l_pe_rec.vesting_flag,
x_expense_account_id => l_p_new_plan_element_rec.expense_account_id,
x_liability_account_id => l_p_new_plan_element_rec.liability_account_id,
x_quota_group_code => l_p_new_plan_element_rec.quota_group_code
--clku PAYMENT ENHANCEMENT,
,
x_payment_group_code => l_p_new_plan_element_rec.payment_group_code,
x_quota_unspecified => NULL,
x_last_update_date => g_last_update_date,
x_last_updated_by => g_last_updated_by,
x_creation_date => g_creation_date,
x_created_by => g_created_by,
x_last_update_login => g_last_update_login,
x_program_type => g_program_type,
x_period_type_code => NULL,
x_start_num => NULL,
x_end_num => NULL,
x_addup_from_rev_class_flag => l_pe_rec.addup_from_rev_class_flag
--clku, bug 2854576
,
x_attribute_category => l_p_new_plan_element_rec.attribute_category,
x_attribute1 => l_p_new_plan_element_rec.attribute1,
x_attribute2 => l_p_new_plan_element_rec.attribute2,
x_attribute3 => l_p_new_plan_element_rec.attribute3,
x_attribute4 => l_p_new_plan_element_rec.attribute4,
x_attribute5 => l_p_new_plan_element_rec.attribute5,
x_attribute6 => l_p_new_plan_element_rec.attribute6,
x_attribute7 => l_p_new_plan_element_rec.attribute7,
x_attribute8 => l_p_new_plan_element_rec.attribute8,
x_attribute9 => l_p_new_plan_element_rec.attribute9,
x_attribute10 => l_p_new_plan_element_rec.attribute10,
x_attribute11 => l_p_new_plan_element_rec.attribute11,
x_attribute12 => l_p_new_plan_element_rec.attribute12,
x_attribute13 => l_p_new_plan_element_rec.attribute13,
x_attribute14 => l_p_new_plan_element_rec.attribute14,
x_attribute15 => l_p_new_plan_element_rec.attribute15,
x_salesrep_end_flag => l_p_new_plan_element_rec.sreps_enddated_flag
);
update_period_quotas (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pe_rec => l_pe_rec,
p_pe_rec_old => l_pe_rec_old,
p_period_quotas_rec_tbl =>l_p_period_quotas_rec_tbl,
p_quota_name => l_p_quota_name_old,
p_loading_status => x_loading_status,
x_loading_status => l_loading_status
);
cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('DELETE', l_srp_period_quota_id.srp_period_quota_id,l_srp_period_quota_id.org_id);
cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('INSERT', l_srp_period_quota_id.srp_period_quota_id,l_srp_period_quota_id.org_id, l_number_dim);
SELECT number_dim
INTO l_number_dim_old
FROM cn_calc_formulas
WHERE calc_formula_id = l_pe_rec_old.calc_formula_id;
SELECT number_dim
INTO l_number_dim_new
FROM cn_calc_formulas
WHERE calc_formula_id = l_pe_rec.calc_formula_id;
cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('DELETE', l_srp_period_quota_id.srp_period_quota_id,l_srp_period_quota_id.org_id);
cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('INSERT',
l_srp_period_quota_id.srp_period_quota_id,
l_number_dim_new
);
update_rate_quotas (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pe_rec => l_pe_rec,
p_pe_rec_old => l_pe_rec_old,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
p_quota_name => l_pe_rec.NAME,
p_loading_status => x_loading_status,
x_loading_status => l_loading_status
);
SAVEPOINT update_plan_element;
cn_quota_rules_grp.update_quota_rules (p_api_version => p_api_version,
p_init_msg_list => 'T',
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => NVL (l_pe_rec.NAME, l_p_quota_name_old),
p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
x_loading_status => x_loading_status
);
ELSIF (x_loading_status <> 'CN_UPDATED')
THEN
RAISE fnd_api.g_exc_error;
UPDATE cn_trx_factors
SET event_factor = l_p_trx_factor_rec_tbl (j).event_factor
WHERE quota_rule_id = l_quota_rule_id
AND quota_id = NVL (l_pe_rec.quota_id, l_pe_rec_old.quota_id)
AND trx_type = l_p_trx_factor_rec_tbl (j).trx_type;
IF (x_return_status <> fnd_api.g_ret_sts_success) OR x_loading_status NOT IN ('CN_UPDATED', 'CN_INSERTED')
THEN
RAISE fnd_api.g_exc_error;
cn_quota_rule_uplifts_grp.update_quota_rule_uplift (p_api_version => p_api_version,
p_init_msg_list => 'T',
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => NVL (l_pe_rec.NAME, l_p_quota_name_old),
p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl1,
x_loading_status => x_loading_status
);
ELSIF (x_loading_status <> 'CN_UPDATED')
THEN
RAISE fnd_api.g_exc_error;
cn_period_quotas_grp.update_period_quotas (p_api_version => p_api_version,
p_init_msg_list => 'T',
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => NVL (l_pe_rec.NAME, l_p_quota_name_old),
p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
x_loading_status => x_loading_status
);
ELSIF (x_loading_status <> 'CN_UPDATED')
THEN
RAISE fnd_api.g_exc_error;
cn_rt_quota_asgns_pvt.update_rt_quota_asgns (p_api_version => p_api_version,
p_init_msg_list => 'T',
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => NVL (l_pe_rec.NAME, l_p_quota_name_old),
p_org_id => l_pe_rec.org_id,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
x_loading_status => x_loading_status,
x_object_version_number => l_pe_rec.object_version_number
);
ELSIF (x_loading_status <> 'CN_UPDATED')
THEN
RAISE fnd_api.g_exc_error;
IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'UPDATE_PLAN_ELEMENT', 'A', 'V')
THEN
cn_plan_element_vuhk.update_plan_element_post (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_new_plan_element_rec => l_p_new_plan_element_rec,
p_quota_name_old => l_p_quota_name_old,
p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
x_loading_status => x_loading_status
);
IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'UPDATE_PLAN_ELEMENT', 'A', 'C')
THEN
cn_plan_element_cuhk.update_plan_element_post (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_new_plan_element_rec => l_p_new_plan_element_rec,
p_quota_name_old => l_p_quota_name_old,
p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
x_loading_status => x_loading_status
);
IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'UPDATE_PLAN_ELEMENT', 'M', 'M')
THEN
IF (cn_plan_element_cuhk.ok_to_generate_msg (p_plan_element_rec => l_p_new_plan_element_rec,
p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
p_trx_factor_rec_tbl => l_p_trx_factor_rec_tbl,
p_period_quotas_rec_tbl => l_p_period_quotas_rec_tbl,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
p_plan_element_name => l_p_quota_name_old
)
)
THEN
-- XMLGEN.clearBindValues;
p_action_code => 'U', /* U - Update */
p_bind_data_id => l_bind_data_id,
p_oai_param => NULL,
p_oai_array => l_oai_array,
x_return_code => x_return_status
);
ROLLBACK TO update_plan_element;
ROLLBACK TO update_plan_element;
ROLLBACK TO update_plan_element;
ROLLBACK TO update_plan_element;
ROLLBACK TO update_plan_element;
ROLLBACK TO update_plan_element;
END update_plan_element;
x_loading_status := 'CN_DELETED';
PROCEDURE delete_plan_element (
p_api_version IN NUMBER := 0,
p_init_msg_list IN VARCHAR2 := cn_api.g_false,
p_commit IN VARCHAR2 := cn_api.g_false,
p_validation_level IN NUMBER := cn_api.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_quota_rec IN plan_element_rec_type := g_miss_plan_element_rec,
p_revenue_class_rec_tbl IN revenue_class_rec_tbl_type := g_miss_revenue_class_rec_tbl,
p_rev_uplift_rec_tbl IN rev_uplift_rec_tbl_type := g_miss_rev_uplift_rec_tbl,
p_rt_quota_asgns_rec_tbl IN rt_quota_asgns_rec_tbl_type := g_miss_rt_quota_asgns_rec_tbl,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_quota_id NUMBER;
l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Plan_Element';
g_last_update_date DATE := SYSDATE;
g_last_updated_by NUMBER := fnd_global.user_id;
g_last_update_login NUMBER := fnd_global.login_id;
SAVEPOINT delete_plan_element;
x_loading_status := 'CN_DELETED';
'cn.plsql.cn_plan_element_pub.delete_plan_element.org_validate',
'Validated org_id = ' || l_val_org_id || ' status = '||l_status);
IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DELETE_PLAN_ELEMENT', 'B', 'C')
THEN
cn_plan_element_cuhk.delete_plan_element_pre (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => l_p_quota_name,
p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
x_loading_status => x_loading_status
);
IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DELETE_PLAN_ELEMENT', 'B', 'V')
THEN
cn_plan_element_vuhk.delete_plan_element_pre (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => l_p_quota_name,
p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
x_loading_status => x_loading_status
);
IF (x_return_status = fnd_api.g_ret_sts_success AND x_loading_status = 'CN_DELETED')
THEN
-- This is calling the group API (cnxgqrub.pls)
-- Needs refactoring
-- Check if the Uplift Rec table count is > 0
IF l_p_rev_uplift_rec_tbl.COUNT > 0
THEN
FOR i IN l_p_rev_uplift_rec_tbl.FIRST .. l_p_rev_uplift_rec_tbl.LAST LOOP
l_p_rev_uplift_rec_tbl1(i).org_id := l_p_rev_uplift_rec_tbl(i).org_id;
cn_quota_rule_uplifts_grp.delete_quota_rule_uplift (p_api_version => p_api_version,
p_init_msg_list => 'T',
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => l_p_quota_name,
p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl1,
x_loading_status => x_loading_status
);
cn_rt_quota_asgns_pvt.delete_rt_quota_asgns (p_api_version => p_api_version,
p_init_msg_list => 'T',
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => l_p_quota_name,
p_org_id => p_quota_rec.org_id,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
x_loading_status => x_loading_status
);
cn_quota_rule_pvt.delete_quota_rules (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => l_p_quota_name,
p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
x_loading_status => x_loading_status
);
/* If no Child record is Passed then Delete the Parent Record
The Plan Element. It will cascade the Rest of the Child
Records */
IF l_p_revenue_class_rec_tbl.COUNT = 0 AND l_p_rt_quota_asgns_rec_tbl.COUNT = 0 AND l_p_rev_uplift_rec_tbl.COUNT = 0
THEN
--l_pvt_rec.quota_id := l_quota_id;
cn_plan_element_pvt.delete_plan_element (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_plan_element => l_pvt_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DELETE_PLAN_ELEMENT', 'A', 'V')
THEN
cn_plan_element_vuhk.delete_plan_element_post (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => l_p_quota_name,
p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
x_loading_status => x_loading_status
);
IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DELETE_PLAN_ELEMENT', 'A', 'C')
THEN
cn_plan_element_cuhk.delete_plan_element_post (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quota_name => l_p_quota_name,
p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl,
x_loading_status => x_loading_status
);
IF jtf_usr_hks.ok_to_execute ('CN_PLAN_ELEMENT_PUB', 'DELETE_PLAN_ELEMENT', 'M', 'M')
THEN
IF (cn_plan_element_cuhk.ok_to_generate_msg (p_plan_element_name => l_p_quota_name,
p_revenue_class_rec_tbl => l_p_revenue_class_rec_tbl,
p_rev_uplift_rec_tbl => l_p_rev_uplift_rec_tbl,
p_rt_quota_asgns_rec_tbl => l_p_rt_quota_asgns_rec_tbl
)
)
THEN
-- XMLGEN.clearBindValues;
p_action_code => 'D', /* D - Delete */
p_bind_data_id => l_bind_data_id,
p_oai_param => NULL,
p_oai_array => l_oai_array,
x_return_code => x_return_status
);
ROLLBACK TO delete_plan_element;
ROLLBACK TO delete_plan_element;
ROLLBACK TO delete_plan_element;
END delete_plan_element;
SELECT q.quota_id quota_id,
q.NAME,
q.description,
NULL period_type,
cn_api.get_lkup_meaning (q.quota_type_code, 'QUOTA_TYPE') element_type,
q.target,
cn_api.get_lkup_meaning (q.incentive_type_code, 'INCENTIVE_TYPE') incentive_type,
ct.NAME credit_type,
cf.NAME calc_formula_name,
q.rt_sched_custom_flag,
q.package_name,
q.performance_goal,
q.payment_amount,
q.start_date,
q.end_date,
q.quota_status,
cit.NAME interval_name,
q.payee_assign_flag,
q.vesting_flag,
q.addup_from_rev_class_flag,
q.expense_account_id,
q.liability_account_id,
q.quota_group_code,
q.attribute_category,
q.attribute1,
q.attribute2,
q.attribute3,
q.attribute4,
q.attribute5,
q.attribute6,
q.attribute7,
q.attribute8,
q.attribute9,
q.attribute10,
q.attribute11,
q.attribute12,
q.attribute13,
q.attribute14,
q.attribute15,
-- Bug 2531254
q.payment_group_code,
--CHANTHON:ADding org_id
q.org_id,
q.indirect_credit,
q.salesreps_enddated_flag
FROM cn_quotas q,
cn_credit_types ct,
cn_calc_formulas cf,
cn_interval_types cit
WHERE q.NAME = pe_name
AND q.org_id = p_org_id
--AND q.credit_type_id = ct.credit_type_id(+)
--AND q.calc_formula_id = cf.calc_formula_id(+)
--AND q.interval_type_id = cit.interval_type_id(+)
AND q.credit_type_id = ct.credit_type_id(+)
AND ct.org_id(+) = q.org_id
AND q.calc_formula_id = cf.calc_formula_id(+)
AND cf.org_id(+) = q.org_id
AND q.interval_type_id = cit.interval_type_id(+)
AND cit.org_id(+) = q.org_id
AND delete_flag='N';
SELECT qr.quota_rule_id,
rc.NAME rev_class_name,
qr.target rev_class_target,
qr.payment_amount rev_class_payment_amount,
qr.performance_goal rev_class_performance_goal,
qr.description,
qr.attribute_category,
qr.attribute1,
qr.attribute2,
qr.attribute3,
qr.attribute4,
qr.attribute5,
qr.attribute6,
qr.attribute7,
qr.attribute8,
qr.attribute9,
qr.attribute10,
qr.attribute11,
qr.attribute12,
qr.attribute13,
qr.attribute14,
qr.attribute15,
qr.org_id
FROM cn_revenue_classes rc,
cn_quota_rules qr
WHERE qr.revenue_class_id = rc.revenue_class_id AND quota_id = pe_id;
SELECT NULL rev_class_name,
qru.start_date,
qru.end_date,
qru.payment_factor rev_class_payment_uplift,
qru.quota_factor rev_class_quota_uplift,
qru.attribute_category,
qru.attribute1,
qru.attribute2,
qru.attribute3,
qru.attribute4,
qru.attribute5,
qru.attribute6,
qru.attribute7,
qru.attribute8,
qru.attribute9,
qru.attribute10,
qru.attribute11,
qru.attribute12,
qru.attribute13,
qru.attribute14,
qru.attribute15,
NULL rev_class_name_old,
NULL start_date1,
NULL start_date2,
qru.org_id org_id,
qru.object_version_number object_version_number
FROM cn_quota_rule_uplifts qru
WHERE qru.quota_rule_id = p_quota_rule_id
ORDER BY start_date;
SELECT tf.trx_type,
tf.event_factor,
tf.org_id
-- rev_class_name get it from previous cursor
FROM cn_trx_factors tf
WHERE tf.quota_id = pe_id AND tf.quota_rule_id = p_quota_rule_id;
SELECT cn_api.get_acc_period_name (period_id,org_id) period_name,
period_target,
period_payment,
performance_goal,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
NULL period_name_old,
org_id
FROM cn_period_quotas
WHERE quota_id = pe_id;
SELECT cn_api.get_rate_table_name (rate_schedule_id) rate_schedule_name,
cn_chk_plan_element_pkg.get_calc_formula_name (calc_formula_id) calc_formula_name,
start_date,
end_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
NULL rate_schedule_name_old,
NULL start_date1,
NULL start_date2,
org_id
FROM cn_rt_quota_asgns
WHERE quota_id = pe_id
ORDER BY start_date;
SELECT 'Y'
FROM dual where exists
(Select period_id from cn_acc_period_statuses_v
WHERE pe_start_date BETWEEN start_date and end_date
AND period_status IN ('F', 'O') and org_id = p_org_id);
x_loading_status := 'CN_INSERTED';
SELECT quota_id into l_quota_id from cn_quotas_all where org_id = l_org_id and name = l_plan_element_rec.NAME and delete_flag = 'N';
IF (x_return_status = fnd_api.g_ret_sts_success) AND (x_loading_status = 'CN_INSERTED')
OR (x_loading_status = 'PLN_QUOTA_RULE_FACTORS_NOT_100')
THEN
x_loading_status := 'CN_INSERTED';
p_action_code => 'I', /* I - Insert */
p_bind_data_id => l_bind_data_id,
p_oai_param => NULL,
p_oai_array => l_oai_array,
x_return_code => x_return_status
);