[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pe.quota_id,
pe.NAME,
pe.description,
pe.quota_type_code,
pe.target,
pe.payment_amount,
pe.performance_goal,
pe.incentive_type_code,
pe.start_date,
pe.end_date,
pe.credit_type_id,
pe.interval_type_id,
pe.calc_formula_id,
pe.liability_account_id,
pe.expense_account_id,
'liability_account_cc',
'expense_account_cc',
pe.vesting_flag,
pe.quota_group_code,
pe.payment_group_code,
pe.attribute_category,
pe.attribute1,
pe.attribute2,
pe.attribute3,
pe.attribute4,
pe.attribute5,
pe.attribute6,
pe.attribute7,
pe.attribute8,
pe.attribute9,
pe.attribute10,
pe.attribute11,
pe.attribute12,
pe.attribute13,
pe.attribute14,
pe.attribute15,
pe.addup_from_rev_class_flag,
pe.payee_assign_flag,
pe.package_name,
pe.object_version_number,
pe.org_id,
pe.indirect_credit,
pe.quota_status,
pe.salesreps_enddated_flag,
NULL
FROM cn_quotas_v pe
WHERE pe.quota_id = p_quota_id;
fnd_message.set_name ('CN', 'CN_INVALID_UPDATE_REC');
SELECT 1
INTO l_dummy
FROM DUAL
WHERE EXISTS (SELECT 1
FROM cn_quotas_v
WHERE quota_id = p_quota_id AND org_id = p_org_id);
IF (p_operation <> 'update') THEN
IF (p_operation = 'create') THEN
fnd_message.set_name('CN','CNR12_NOTE_PE_NAME_CREATE');
IF (p_operation = 'delete') THEN
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_NAME_DELETE');
l_temp_new := 'CN_DELETED_OBJECTS';
ELSIF (p_operation = 'update') THEN
l_consolidated_note := '';
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_NAME_UPDATE');
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_STDATE_UPDATE');
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_ENDATE_UPDATE');
fnd_message.set_name ('CN','CNR12_NOTE_PE_DESC_UPDATE');
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_INTTYPE_UPDATE');
select NAME into l_temp_old from CN_INTERVAL_TYPES
where interval_type_id = p_plan_element_old.interval_type_id
and org_id = p_plan_element_old.org_id;
select NAME into l_temp_new from CN_INTERVAL_TYPES
where interval_type_id = p_plan_element_new.interval_type_id
and org_id = p_plan_element_new.org_id;
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_FORTYPE_UPDATE');
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_FOR_TYPE_UPDATE');
SELECT NAME INTO l_temp_old FROM CN_CALC_FORMULAS
WHERE CALC_FORMULA_ID = p_plan_element_old.calc_formula_id;
SELECT NAME INTO l_temp_new FROM CN_CALC_FORMULAS
WHERE CALC_FORMULA_ID = p_plan_element_new.calc_formula_id;
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_PAYGRP_UPDATE');
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_CRTYPE_UPDATE');
select name into l_temp_old from cn_credit_types_vl
where credit_type_id = p_plan_element_old.credit_type_id
and org_id = p_plan_element_old.org_id;
select name into l_temp_new from cn_credit_types_vl
where credit_type_id = p_plan_element_new.credit_type_id
and org_id = p_plan_element_new.org_id;
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_THIRDPARY_UPDATE');
select meaning into l_temp_old from cn_lookups
where lookup_code = NVL(p_plan_element_old.payee_assign_flag, 'N')
and lookup_type = 'YES_NO';
select meaning into l_temp_new from cn_lookups
where lookup_code = NVL(p_plan_element_new.payee_assign_flag, 'N')
and lookup_type = 'YES_NO';
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_LIA_UPDATE');
SELECT
DECODE(LA.CODE_COMBINATION_ID,NULL,NULL, LA.SEGMENT1||'-'||LA.SEGMENT2||'-'||LA.SEGMENT3||'-'||LA.SEGMENT4 ||'-'||LA.SEGMENT5)
INTO l_temp_old FROM GL_CODE_COMBINATIONS LA
WHERE LA.CODE_COMBINATION_ID = p_plan_element_old.liability_account_id;
SELECT
DECODE(LA.CODE_COMBINATION_ID,NULL,NULL, LA.SEGMENT1||'-'||LA.SEGMENT2||'-'||LA.SEGMENT3||'-'||LA.SEGMENT4 ||'-'||LA.SEGMENT5)
INTO l_temp_new FROM GL_CODE_COMBINATIONS LA
WHERE LA.CODE_COMBINATION_ID = p_plan_element_new.liability_account_id;
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_EXP_UPDATE');
SELECT
DECODE(LA.CODE_COMBINATION_ID,NULL,NULL, LA.SEGMENT1||'-'||LA.SEGMENT2||'-'||LA.SEGMENT3||'-'||LA.SEGMENT4 ||'-'||LA.SEGMENT5)
INTO l_temp_old FROM GL_CODE_COMBINATIONS LA
WHERE LA.CODE_COMBINATION_ID = p_plan_element_old.expense_account_id;
SELECT
DECODE(LA.CODE_COMBINATION_ID,NULL,NULL, LA.SEGMENT1||'-'||LA.SEGMENT2||'-'||LA.SEGMENT3||'-'||LA.SEGMENT4 ||'-'||LA.SEGMENT5)
INTO l_temp_new FROM GL_CODE_COMBINATIONS LA
WHERE LA.CODE_COMBINATION_ID = p_plan_element_new.expense_account_id;
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_CRROLL_UPDATE');
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 plan_element_rec_type,
p_pe_rec_old IN plan_element_rec_type,
p_rt_quota_asgns_rec_tbl IN cn_plan_element_pub.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_rt_quota_asgns_rec_tbl => p_rt_quota_asgns_rec_tbl,
x_loading_status => x_loading_status
);*/
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_rt_quota_asgns_rec_tbl => p_rt_quota_asgns_rec_tbl,
x_loading_status => x_loading_status,
p_org_id => p_pe_rec.org_id,
x_object_version_number => l_object_version_number
);
END update_rate_quotas;
PROCEDURE update_exprs (
p_quota_id NUMBER,
p_old_name VARCHAR2,
p_new_name VARCHAR2
)
IS
CURSOR get_exps
IS
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;
END update_exprs;
SELECT COUNT (*)
INTO l_tmp_exist
FROM cn_lookups
WHERE lookup_type = l_lookup_type
AND lookup_code = p_plan_element.incentive_type_code;
SELECT COUNT (*)
INTO l_tmp_exist
FROM cn_lookups
WHERE lookup_type = l_lookup_type
AND lookup_code = p_plan_element.payment_group_code;
SELECT lookup_code
INTO l_temp
FROM cn_lookups
WHERE lookup_type = 'QUOTA_GROUP_CODE' AND lookup_code = p_plan_element.quota_group_code;
SELECT lookup_code
INTO l_temp
FROM cn_lookups
WHERE lookup_type = 'PLAN_ELEMENT_STATUS_TYPE' AND lookup_code = p_plan_element.quota_status;
SELECT COUNT (*)
INTO l_tmp_exist
FROM cn_lookups
WHERE lookup_type = l_lookup_type
AND lookup_code = p_plan_element.indirect_credit_code;
SELECT COUNT (1)
INTO l_tmp_exist
FROM cn_credit_types
WHERE credit_type_id = p_plan_element.credit_type_id AND org_id = p_plan_element.org_id;
SELECT COUNT (*)
INTO l_tmp_exist
FROM cn_interval_types
WHERE interval_type_id = p_plan_element.interval_type_id AND org_id = p_plan_element.org_id;
SELECT chart_of_accounts_id
INTO l_chart_of_accounts_id
FROM gl_sets_of_books gsb,
cn_repositories cr
WHERE cr.set_of_books_id = gsb.set_of_books_id;
sql_stmt := 'SELECT COUNT(*)';
sql_stmt := 'SELECT code_combination_id';
PROCEDURE validate_and_update_ccids (
p_plan_element IN OUT NOCOPY plan_element_rec_type
)
IS
l_id NUMBER;
SELECT code_combination_id
INTO l_id
FROM gl_code_combinations
WHERE code_combination_id = p_plan_element.liability_account_id AND account_type = 'L';
SELECT code_combination_id
INTO l_id
FROM gl_code_combinations
WHERE code_combination_id = p_plan_element.expense_account_id AND account_type = 'E';
END validate_and_update_ccids;
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 cn_quotas_s.NEXTVAL
INTO p_plan_element.quota_id
FROM DUAL;
validate_and_update_ccids (p_plan_element);
cn_quotas_pkg.begin_record (x_operation => 'INSERT',
x_rowid => g_remove_this,
x_quota_id => p_plan_element.quota_id,
x_object_version_number => p_plan_element.object_version_number,
x_name => p_plan_element.NAME,
x_target => NVL (p_plan_element.target, 0),
x_quota_type_code => p_plan_element.quota_type_code,
x_usage_code => NULL,
x_payment_amount => NVL (p_plan_element.payment_amount, 0),
x_description => p_plan_element.description,
x_start_date => p_plan_element.start_date,
x_end_date => p_plan_element.end_date,
x_quota_status => p_plan_element.quota_status,
x_calc_formula_id => p_plan_element.calc_formula_id,
x_incentive_type_code => p_plan_element.incentive_type_code,
x_credit_type_id => p_plan_element.credit_type_id,
x_rt_sched_custom_flag => NULL,
x_package_name => p_plan_element.package_name,
x_performance_goal => NVL (p_plan_element.performance_goal, 0),
x_interval_type_id => p_plan_element.interval_type_id,
x_payee_assign_flag => p_plan_element.payee_assign_flag,
x_vesting_flag => p_plan_element.vesting_flag,
x_expense_account_id => p_plan_element.expense_account_id,
x_liability_account_id => p_plan_element.liability_account_id,
x_quota_group_code => p_plan_element.quota_group_code,
--clku PAYMENT ENHANCEMENT
x_payment_group_code => p_plan_element.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 => NULL,
--x_status_code => NULL,
x_period_type_code => NULL,
x_start_num => NULL,
x_end_num => NULL,
x_addup_from_rev_class_flag => p_plan_element.addup_from_rev_class_flag,
x_attribute_category => p_plan_element.attribute_category,
x_attribute1 => p_plan_element.attribute1,
x_attribute2 => p_plan_element.attribute2,
x_attribute3 => p_plan_element.attribute3,
x_attribute4 => p_plan_element.attribute4,
x_attribute5 => p_plan_element.attribute5,
x_attribute6 => p_plan_element.attribute6,
x_attribute7 => p_plan_element.attribute7,
x_attribute8 => p_plan_element.attribute8,
x_attribute9 => p_plan_element.attribute9,
x_attribute10 => p_plan_element.attribute10,
x_attribute11 => p_plan_element.attribute11,
x_attribute12 => p_plan_element.attribute12,
x_attribute13 => p_plan_element.attribute13,
x_attribute14 => p_plan_element.attribute14,
x_attribute15 => p_plan_element.attribute15,
x_indirect_credit => p_plan_element.indirect_credit_code,
x_org_id => p_plan_element.org_id,
x_salesrep_end_flag => p_plan_element.sreps_enddated_flag
);
cn_rt_quota_asgns_pkg.INSERT_RECORD (x_quota_id => p_plan_element.quota_id, x_calc_formula_id => p_plan_element.calc_formula_id);
PROCEDURE update_plan_element (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_plan_element IN OUT NOCOPY plan_element_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR l_old_plan_element_cr (
qid NUMBER
)
IS
SELECT pe.quota_id,
pe.NAME,
pe.description,
pe.quota_type_code,
pe.target,
pe.payment_amount,
pe.performance_goal,
pe.incentive_type_code,
pe.start_date,
pe.end_date,
pe.credit_type_id,
pe.interval_type_id,
pe.calc_formula_id,
pe.liability_account_id,
pe.expense_account_id,
'liability_account_cc',
'expense_account_cc',
pe.vesting_flag,
pe.quota_group_code,
pe.payment_group_code,
pe.attribute_category,
pe.attribute1,
pe.attribute2,
pe.attribute3,
pe.attribute4,
pe.attribute5,
pe.attribute6,
pe.attribute7,
pe.attribute8,
pe.attribute9,
pe.attribute10,
pe.attribute11,
pe.attribute12,
pe.attribute13,
pe.attribute14,
pe.attribute15,
pe.addup_from_rev_class_flag,
pe.payee_assign_flag,
pe.package_name,
pe.object_version_number,
pe.org_id,
pe.indirect_credit,
pe.quota_status,
pe.salesreps_enddated_flag,
NULL
FROM cn_quotas_v pe
WHERE pe.quota_id = qid;
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;
SELECT srp_period_quota_id
FROM cn_srp_period_quotas
WHERE quota_id = pe_quota_id;
g_last_update_date DATE := SYSDATE;
g_last_updated_by NUMBER := fnd_global.user_id;
g_last_update_login NUMBER := fnd_global.login_id;
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Plan_Element';
SAVEPOINT update_plan_element;
p_action => 'UPDATE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT SUM (target)
INTO p_plan_element.target
FROM cn_quota_rules
WHERE quota_id = p_plan_element.quota_id;
SELECT SUM (payment_amount)
INTO p_plan_element.payment_amount
FROM cn_quota_rules
WHERE quota_id = p_plan_element.quota_id;
SELECT SUM (performance_goal)
INTO p_plan_element.performance_goal
FROM cn_quota_rules
WHERE quota_id = p_plan_element.quota_id;
validate_and_update_ccids (p_plan_element);
cn_quotas_pkg.begin_record (x_operation => 'UPDATE',
x_rowid => g_row_id,
x_quota_id => p_plan_element.quota_id,
x_object_version_number => p_plan_element.object_version_number,
x_name => p_plan_element.NAME,
x_target => NVL (p_plan_element.target, 0),
x_quota_type_code => p_plan_element.quota_type_code,
x_usage_code => NULL,
x_payment_amount => NVL (p_plan_element.payment_amount, 0),
x_description => p_plan_element.description,
x_start_date => p_plan_element.start_date,
x_end_date => p_plan_element.end_date,
x_quota_status => p_plan_element.quota_status,
x_calc_formula_id => p_plan_element.calc_formula_id,
x_incentive_type_code => p_plan_element.incentive_type_code,
x_credit_type_id => p_plan_element.credit_type_id,
x_rt_sched_custom_flag => NULL,
x_package_name => p_plan_element.package_name,
x_performance_goal => NVL (p_plan_element.performance_goal, 0),
x_interval_type_id => p_plan_element.interval_type_id,
x_payee_assign_flag => p_plan_element.payee_assign_flag,
x_vesting_flag => p_plan_element.vesting_flag,
x_expense_account_id => p_plan_element.expense_account_id,
x_liability_account_id => p_plan_element.liability_account_id,
x_quota_group_code => p_plan_element.quota_group_code,
x_payment_group_code => p_plan_element.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 => NULL,
x_created_by => NULL,
x_last_update_login => g_last_update_login,
x_program_type => NULL,
--x_status_code => p_plan_element.quota_status,
x_period_type_code => NULL,
x_start_num => NULL,
x_end_num => NULL,
x_addup_from_rev_class_flag => p_plan_element.addup_from_rev_class_flag,
x_attribute_category => p_plan_element.attribute_category,
x_attribute1 => p_plan_element.attribute1,
x_attribute2 => p_plan_element.attribute2,
x_attribute3 => p_plan_element.attribute3,
x_attribute4 => p_plan_element.attribute4,
x_attribute5 => p_plan_element.attribute5,
x_attribute6 => p_plan_element.attribute6,
x_attribute7 => p_plan_element.attribute7,
x_attribute8 => p_plan_element.attribute8,
x_attribute9 => p_plan_element.attribute9,
x_attribute10 => p_plan_element.attribute10,
x_attribute11 => p_plan_element.attribute11,
x_attribute12 => p_plan_element.attribute12,
x_attribute13 => p_plan_element.attribute13,
x_attribute14 => p_plan_element.attribute14,
x_attribute15 => p_plan_element.attribute15,
x_indirect_credit => p_plan_element.indirect_credit_code,
x_org_id => p_plan_element.org_id,
x_salesrep_end_flag =>p_plan_element.sreps_enddated_flag
);
update_exprs (p_plan_element.quota_id, l_old_plan_element.NAME, p_plan_element.NAME);
cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('DELETE',
l_srp_period_quota_id.srp_period_quota_id,
l_old_plan_element.org_id
);
cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('INSERT',
l_srp_period_quota_id.srp_period_quota_id,
l_old_plan_element.org_id,
l_number_dim
);
SELECT number_dim
INTO l_number_dim_old
FROM cn_calc_formulas
WHERE calc_formula_id = l_old_plan_element.calc_formula_id;
SELECT number_dim
INTO l_number_dim_new
FROM cn_calc_formulas
WHERE calc_formula_id = p_plan_element.calc_formula_id;
cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('DELETE',
l_srp_period_quota_id.srp_period_quota_id,
l_old_plan_element.org_id
);
cn_srp_period_quotas_pkg.populate_srp_period_quotas_ext ('INSERT',
l_srp_period_quota_id.srp_period_quota_id,
l_old_plan_element.org_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 => p_plan_element,
p_pe_rec_old => l_old_plan_element,
p_rt_quota_asgns_rec_tbl => g_miss_rt_quota_asgns_rec_tbl,
p_quota_name => p_plan_element.NAME,
p_loading_status => x_loading_status,
x_loading_status => l_loading_status
);
'update',
x_return_status,
x_msg_count,
x_msg_data
);
ROLLBACK TO update_plan_element;
ROLLBACK TO update_plan_element;
ROLLBACK TO update_plan_element;
END update_plan_element;
PROCEDURE delete_plan_element (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_plan_element IN OUT NOCOPY plan_element_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Plan_Element';
SAVEPOINT delete_plan_element;
p_action => 'DELETE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT NAME
INTO l_quota_name
FROM cn_quotas_v
WHERE quota_id = p_plan_element.quota_id;
cn_quotas_pkg.DELETE_RECORD (x_quota_id => p_plan_element.quota_id, x_name => l_quota_name);
'delete',
x_return_status,
x_msg_count,
x_msg_data
);
ROLLBACK TO delete_plan_element;
ROLLBACK TO delete_plan_element;
ROLLBACK TO delete_plan_element;
END delete_plan_element;
SELECT COUNT (1)
FROM cn_quota_rule_uplifts qru,
cn_quota_rules qr
WHERE qr.quota_id = p_quota_id
AND qr.quota_rule_id = qru.quota_rule_id
AND (qru.start_date < p_start_date OR (p_end_date IS NOT NULL AND qru.end_date IS NULL) OR qru.end_date > p_end_date);
IF (p_action = 'DELETE')
THEN
SELECT COUNT (*)
INTO l_temp_count
FROM cn_quotas_v
WHERE quota_id = p_plan_element.quota_id;
SELECT 1
INTO l_temp_count
FROM SYS.DUAL
WHERE NOT EXISTS (SELECT 1
FROM cn_quota_assigns
WHERE quota_id = p_plan_element.quota_id);
fnd_message.set_name ('CN', 'PLN_QUOTA_DELETE_NA');
IF (p_action = 'UPDATE')
THEN
-- better check that org_id first or you will cry
IF NOT is_valid_org (p_plan_element.org_id, p_plan_element.quota_id)
THEN
RAISE fnd_api.g_exc_error;
SELECT COUNT (1)
INTO l_temp_count
FROM cn_quotas_all pe
WHERE NAME = p_plan_element.NAME AND p_plan_element.quota_id <> pe.quota_id AND p_plan_element.org_id = pe.org_id AND delete_flag = 'N';
SELECT COUNT (1)
INTO l_temp_count
FROM cn_quotas_all pe
WHERE NAME = p_plan_element.NAME AND org_id = p_plan_element.org_id AND delete_flag = 'N';
SELECT COUNT (*)
INTO l_payeechk
FROM cn_quota_assigns cqa
WHERE cqa.quota_id = p_plan_element.quota_id
AND EXISTS (SELECT 1
FROM cn_srp_plan_assigns cspa
WHERE cspa.comp_plan_id = cqa.comp_plan_id AND EXISTS (SELECT 1
FROM cn_srp_roles csr
WHERE csr.salesrep_id = cspa.salesrep_id AND csr.role_id = 54));
END IF; -- END OF DELETE VALIDATION
SELECT qa.rate_schedule_id
FROM cn_rt_quota_asgns qa
WHERE qa.quota_id = pe_quota_id;
SELECT rtq.calc_formula_id
FROM cn_rt_quota_asgns rtq
WHERE rtq.quota_id = pe_quota_id AND rtq.rate_schedule_id = pe_rate_schedule_id;
SELECT fi.formula_input_id
FROM cn_formula_inputs fi
WHERE fi.calc_formula_id = pe_calc_formula_id;
SELECT cumulative_flag,
split_flag,
rate_dim_sequence
INTO l_cumulative_flag,
l_split_flag,
l_rate_dim_sequence
FROM cn_formula_inputs
WHERE formula_input_id = l_formula_input_id.formula_input_id;
SELECT cd.dim_unit_code
INTO l_dim_unit_code
FROM cn_rate_dimensions cd,
cn_rate_sch_dims cs
WHERE cs.rate_dim_sequence = l_rate_dim_sequence
AND cs.rate_schedule_id = l_rate_schedule_id.rate_schedule_id
AND cd.rate_dimension_id = cs.rate_dimension_id;
SELECT NAME
INTO l_quota_name
FROM cn_quotas
WHERE quota_id = p_quota_id;
SELECT rqa.rate_schedule_id,
rs.NAME
FROM cn_rt_quota_asgns rqa,
cn_rate_schedules rs
WHERE rqa.quota_id = p_quota_id AND rqa.rate_schedule_id = rs.rate_schedule_id;
SELECT qr.quota_rule_id,
qr.revenue_class_id,
rc.NAME rev_class_name,
q.quota_type_code
FROM cn_quotas q,
cn_quota_rules_all qr,
cn_revenue_classes_all rc
WHERE qr.quota_id = p_quota_id
AND qr.revenue_class_id = rc.revenue_class_id
AND q.quota_id = qr.quota_id
AND q.quota_type_code IN ('FORMULA', 'EXTERNAL');
SELECT event_factor,
trx_type
FROM cn_trx_factors
WHERE quota_rule_id = p_quota_rule_id;
p_action => 'UPDATE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT NAME
INTO l_plan_name
FROM cn_comp_plans
WHERE comp_plan_id = p_comp_plan_id;
SELECT cf.NAME,
q.calc_formula_id
INTO x_formula_name,
x_calc_formula_id
FROM cn_quotas q,
cn_calc_formulas cf
WHERE q.quota_id = p_quota_id AND q.calc_formula_id = cf.calc_formula_id(+) AND q.quota_type_code IN ('EXTERNAL', 'FORMULA');
SELECT COUNT (1)
INTO l_tmp
FROM cn_rt_quota_asgns
WHERE quota_id = p_quota_id;