[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
ELSIF (p_operation = 'update') THEN
l_key := l_key || '-' || p_comp_plan.OBJECT_VERSION_NUMBER;
ELSIF (p_operation = 'delete') THEN
wf_event.AddParameterToList('COMP_PLAN_ID',p_comp_plan.COMP_PLAN_ID,l_list);
l_list.DELETE;
SELECT object_version_number
INTO l_num
FROM cn_comp_plans_all
WHERE comp_plan_id = p_id;
SELECT COUNT (1)
INTO l_temp_count
FROM cn_comp_plans
WHERE NAME = p_comp_plan.NAME AND org_id = p_comp_plan.org_id AND ROWNUM = 1;
SELECT DECODE (p_comp_plan.NAME, fnd_api.g_miss_char, NULL, p_comp_plan.NAME),
DECODE (p_comp_plan.description, fnd_api.g_miss_char, NULL, p_comp_plan.description),
'INCOMPLETE',
DECODE (p_comp_plan.allow_rev_class_overlap, fnd_api.g_miss_char, NULL, p_comp_plan.allow_rev_class_overlap),
DECODE (p_comp_plan.start_date, fnd_api.g_miss_date, NULL, TRUNC (p_comp_plan.start_date)),
DECODE (p_comp_plan.end_date, fnd_api.g_miss_date, NULL, TRUNC (p_comp_plan.end_date)),
NULL,
DECODE (p_comp_plan.attribute_category, fnd_api.g_miss_char, NULL, p_comp_plan.attribute_category),
DECODE (p_comp_plan.attribute1, fnd_api.g_miss_char, NULL, p_comp_plan.attribute1),
DECODE (p_comp_plan.attribute2, fnd_api.g_miss_char, NULL, p_comp_plan.attribute2),
DECODE (p_comp_plan.attribute3, fnd_api.g_miss_char, NULL, p_comp_plan.attribute3),
DECODE (p_comp_plan.attribute4, fnd_api.g_miss_char, NULL, p_comp_plan.attribute4),
DECODE (p_comp_plan.attribute5, fnd_api.g_miss_char, NULL, p_comp_plan.attribute5),
DECODE (p_comp_plan.attribute6, fnd_api.g_miss_char, NULL, p_comp_plan.attribute6),
DECODE (p_comp_plan.attribute7, fnd_api.g_miss_char, NULL, p_comp_plan.attribute7),
DECODE (p_comp_plan.attribute8, fnd_api.g_miss_char, NULL, p_comp_plan.attribute8),
DECODE (p_comp_plan.attribute9, fnd_api.g_miss_char, NULL, p_comp_plan.attribute9),
DECODE (p_comp_plan.attribute10, fnd_api.g_miss_char, NULL, p_comp_plan.attribute10),
DECODE (p_comp_plan.attribute11, fnd_api.g_miss_char, NULL, p_comp_plan.attribute11),
DECODE (p_comp_plan.attribute12, fnd_api.g_miss_char, NULL, p_comp_plan.attribute12),
DECODE (p_comp_plan.attribute13, fnd_api.g_miss_char, NULL, p_comp_plan.attribute13),
DECODE (p_comp_plan.attribute14, fnd_api.g_miss_char, NULL, p_comp_plan.attribute14),
DECODE (p_comp_plan.attribute15, fnd_api.g_miss_char, NULL, p_comp_plan.attribute15),
DECODE (p_comp_plan.org_id, fnd_api.g_miss_char, NULL, p_comp_plan.org_id)
INTO l_comp_rec.NAME,
l_comp_rec.description,
l_comp_rec.status,
l_comp_rec.rc_overlap,
l_comp_rec.start_date,
l_comp_rec.end_date,
l_comp_rec.plan_element_name,
l_comp_rec.attribute_category,
l_comp_rec.attribute1,
l_comp_rec.attribute2,
l_comp_rec.attribute3,
l_comp_rec.attribute4,
l_comp_rec.attribute5,
l_comp_rec.attribute6,
l_comp_rec.attribute7,
l_comp_rec.attribute8,
l_comp_rec.attribute9,
l_comp_rec.attribute10,
l_comp_rec.attribute11,
l_comp_rec.attribute12,
l_comp_rec.attribute13,
l_comp_rec.attribute14,
l_comp_rec.attribute15,
l_comp_rec.org_id
FROM DUAL;
PROCEDURE update_comp_plan (
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_comp_plan IN OUT NOCOPY comp_plan_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) := 'Update_Comp_Plan';
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 *
FROM cn_comp_plans
WHERE comp_plan_id = p_comp_plan.comp_plan_id;
SAVEPOINT update_comp_plan;
SELECT COUNT (1)
INTO l_temp_count
FROM cn_comp_plans
WHERE NAME = p_comp_plan.NAME AND comp_plan_id <> p_comp_plan.comp_plan_id AND org_id = p_comp_plan.org_id AND ROWNUM = 1;
SELECT DECODE (p_comp_plan.start_date, fnd_api.g_miss_date, TRUNC (l_old_comp_plan.start_date), TRUNC (p_comp_plan.start_date)),
DECODE (p_comp_plan.end_date, fnd_api.g_miss_date, TRUNC (l_old_comp_plan.end_date), TRUNC (p_comp_plan.end_date))
INTO l_start_date,
l_end_date
FROM DUAL;
SELECT DECODE (p_comp_plan.NAME, fnd_api.g_miss_char, l_old_comp_plan.NAME, p_comp_plan.NAME),
DECODE (p_comp_plan.description, fnd_api.g_miss_char, l_old_comp_plan.description, p_comp_plan.description),
DECODE (p_comp_plan.allow_rev_class_overlap,
fnd_api.g_miss_char, l_old_comp_plan.allow_rev_class_overlap,
p_comp_plan.allow_rev_class_overlap
),
DECODE (p_comp_plan.attribute_category, fnd_api.g_miss_char, l_old_comp_plan.attribute_category, p_comp_plan.attribute_category),
DECODE (p_comp_plan.attribute1, fnd_api.g_miss_char, l_old_comp_plan.attribute1, p_comp_plan.attribute1),
DECODE (p_comp_plan.attribute2, fnd_api.g_miss_char, l_old_comp_plan.attribute2, p_comp_plan.attribute2),
DECODE (p_comp_plan.attribute3, fnd_api.g_miss_char, l_old_comp_plan.attribute3, p_comp_plan.attribute3),
DECODE (p_comp_plan.attribute4, fnd_api.g_miss_char, l_old_comp_plan.attribute4, p_comp_plan.attribute4),
DECODE (p_comp_plan.attribute5, fnd_api.g_miss_char, l_old_comp_plan.attribute5, p_comp_plan.attribute5),
DECODE (p_comp_plan.attribute6, fnd_api.g_miss_char, l_old_comp_plan.attribute6, p_comp_plan.attribute6),
DECODE (p_comp_plan.attribute7, fnd_api.g_miss_char, l_old_comp_plan.attribute7, p_comp_plan.attribute7),
DECODE (p_comp_plan.attribute8, fnd_api.g_miss_char, l_old_comp_plan.attribute8, p_comp_plan.attribute8),
DECODE (p_comp_plan.attribute9, fnd_api.g_miss_char, l_old_comp_plan.attribute9, p_comp_plan.attribute9),
DECODE (p_comp_plan.attribute10, fnd_api.g_miss_char, l_old_comp_plan.attribute10, p_comp_plan.attribute10),
DECODE (p_comp_plan.attribute11, fnd_api.g_miss_char, l_old_comp_plan.attribute11, p_comp_plan.attribute11),
DECODE (p_comp_plan.attribute12, fnd_api.g_miss_char, l_old_comp_plan.attribute12, p_comp_plan.attribute12),
DECODE (p_comp_plan.attribute13, fnd_api.g_miss_char, l_old_comp_plan.attribute13, p_comp_plan.attribute13),
DECODE (p_comp_plan.attribute14, fnd_api.g_miss_char, l_old_comp_plan.attribute14, p_comp_plan.attribute14),
DECODE (p_comp_plan.attribute15, fnd_api.g_miss_char, l_old_comp_plan.attribute15, p_comp_plan.attribute15),
DECODE (p_comp_plan.org_id, fnd_api.g_miss_char, l_old_comp_plan.org_id, p_comp_plan.org_id)
INTO l_name,
l_description,
l_overlap,
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15,
l_org_id
FROM DUAL;
SELECT COUNT (1)
INTO l_temp_count
FROM cn_quotas_v q,
cn_quota_assigns qa
WHERE q.quota_id = qa.quota_id
AND qa.comp_plan_id = l_comp_plan_id
AND GREATEST (start_date, l_start_date) > LEAST (NVL (end_date, l_end_date), l_end_date);
SELECT COUNT (1)
INTO l_temp_count
FROM cn_role_plans
WHERE comp_plan_id = l_comp_plan_id AND (start_date < l_start_date OR (end_date IS NULL AND l_end_date IS NOT NULL) OR (end_date > l_end_date));
SELECT org_id
INTO l_org_id
FROM cn_comp_plans
WHERE comp_plan_id = l_comp_plan_id;
(p_operation => 'update',
p_pre_or_post => 'pre',
p_comp_plan => p_comp_plan);
cn_comp_plans_pkg.begin_record (x_operation => 'UPDATE',
x_rowid => g_rowid,
x_comp_plan_id => l_comp_plan_id,
x_name => l_name,
x_description => l_description,
x_start_date => l_start_date,
x_end_date => l_end_date,
x_status_code => 'INCOMPLETE',
x_allow_rev_class_overlap => l_overlap,
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 => 'PL/SQL',
x_start_date_old => l_old_comp_plan.start_date,
x_end_date_old => l_old_comp_plan.end_date,
x_allow_rev_class_overlap_old => l_old_comp_plan.allow_rev_class_overlap,
x_attribute_category => l_attribute_category,
x_attribute1 => l_attribute1,
x_attribute2 => l_attribute2,
x_attribute3 => l_attribute3,
x_attribute4 => l_attribute4,
x_attribute5 => l_attribute5,
x_attribute6 => l_attribute6,
x_attribute7 => l_attribute7,
x_attribute8 => l_attribute8,
x_attribute9 => l_attribute9,
x_attribute10 => l_attribute10,
x_attribute11 => l_attribute11,
x_attribute12 => l_attribute12,
x_attribute13 => l_attribute13,
x_attribute14 => l_attribute14,
x_attribute15 => l_attribute15,
x_org_id => l_org_id
);
(p_operation => 'update',
p_pre_or_post => 'post',
p_comp_plan => p_comp_plan);
fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_UPDATE');
ROLLBACK TO update_comp_plan;
ROLLBACK TO update_comp_plan;
ROLLBACK TO update_comp_plan;
END update_comp_plan;
PROCEDURE delete_comp_plan (
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_comp_plan IN OUT NOCOPY comp_plan_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_Comp_Plan';
SAVEPOINT delete_comp_plan;
SELECT NAME INTO l_cp_name from CN_COMP_PLANS where comp_plan_id = l_comp_plan_id;
(p_operation => 'delete',
p_pre_or_post => 'pre',
p_comp_plan => p_comp_plan);
cn_comp_plans_pkg.begin_record (x_operation => 'DELETE',
x_rowid => l_dummy_row_id,
x_comp_plan_id => l_comp_plan_id,
x_name => NULL,
x_last_update_date => NULL,
x_last_updated_by => NULL,
x_creation_date => NULL,
x_created_by => NULL,
x_last_update_login => NULL,
x_description => NULL,
x_start_date => NULL,
x_start_date_old => NULL,
x_end_date => NULL,
x_end_date_old => NULL,
x_program_type => 'API',
x_status_code => NULL,
x_allow_rev_class_overlap => NULL,
x_allow_rev_class_overlap_old => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_org_id => NULL
);
(p_operation => 'delete',
p_pre_or_post => 'post',
p_comp_plan => p_comp_plan);
fnd_message.set_name ('CN', 'CNR12_NOTE_COMPPLAN_DELETE');
p_source_object_code => 'CN_DELETED_OBJECTS',
p_notes => l_note_msg,
p_notes_detail => l_note_msg,
p_note_type => 'CN_SYSGEN', -- for system generated
x_jtf_note_id => l_note_id -- returned
);
ROLLBACK TO delete_comp_plan;
ROLLBACK TO delete_comp_plan;
ROLLBACK TO delete_comp_plan;
END delete_comp_plan;
SELECT *
FROM cn_comp_plans
WHERE UPPER (NAME) LIKE UPPER (p_search_name)
AND status_code = DECODE (p_search_status, 'NULL', status_code, p_search_status)
AND TRUNC (start_date) >= TRUNC (NVL (p_search_date, start_date))
ORDER BY NAME;
SELECT *
FROM cn_comp_plans
WHERE comp_plan_id = p_comp_plan_id;
SELECT r.NAME,
r.description,
p.start_date,
p.end_date,
p.object_version_number,
r.role_id
FROM cn_role_plans p,
cn_roles r
WHERE p.comp_plan_id = p_comp_plan_id AND r.role_id = p.role_id;
SELECT *
FROM cn_comp_plans
WHERE comp_plan_id = p_comp_plan.comp_plan_id;
SELECT f.NAME
FROM cn_quota_assigns qa,
cn_quotas_v q,
cn_calc_formulas f
WHERE qa.comp_plan_id = p_comp_plan.comp_plan_id
AND q.quota_id = qa.quota_id
AND q.calc_formula_id = f.calc_formula_id
AND f.formula_status = 'INCOMPLETE';
SELECT COUNT (1)
INTO l_temp_count
FROM cn_quota_assigns
WHERE comp_plan_id = p_comp_plan.comp_plan_id AND ROWNUM = 1;
SELECT status_code
INTO l_status_code
FROM cn_comp_plans
WHERE comp_plan_id = p_comp_plan.comp_plan_id;
SELECT count(value_external_id), count( distinct value_external_id)
FROM cn_dim_explosion de,
cn_quota_rules qr,
cn_quota_assigns qa,
cn_quotas_v q,
cn_dim_hier_periods dh,
cn_periods cp
WHERE dh.header_hierarchy_id = cn_global_var.g_rev_class_hierarchy_id
AND cp.start_date >= q.start_date
AND cp.end_date <= nvl(q.end_date,cp.end_date)
AND cp.period_id = dh.period_id
AND de.dim_hierarchy_id = dh.dim_hierarchy_id
AND de.ancestor_external_id = qr.revenue_class_id
AND qr.quota_id = qa.quota_id
AND qa.comp_plan_id = p_comp_plan_id
AND qa.quota_id = q.quota_id
AND q.quota_type_code IN ('EXTERNAL', 'FORMULA')
GROUP BY cp.period_id
HAVING count(value_external_id) <> count( distinct value_external_id)
;
SELECT COUNT (de.value_external_id),COUNT (distinct de.value_external_id)
FROM cn_dim_explosion de,
cn_quota_rules qr,
cn_quota_assigns qa,
(select q1.quota_id, q1.start_date, q1.end_Date
from cn_quotas_v q1, cn_quota_assigns qa
where
qa.comp_plan_id = p_comp_plan_id
and qa.quota_id = q1.quota_id
and exists
(
select 1 from cn_quotas_v q2, cn_quota_assigns qa1
where
qa1.comp_plan_id = p_comp_plan_id
and qa1.quota_id = q2.quota_id
and q1.quota_id <> q2.quota_id
and ((q1.end_date is null OR trunc(q1.end_date) >= trunc(q2.start_Date))
AND (q2.end_date is null OR trunc(q1.start_date) <= trunc(q2.end_date)))
)
) q,
cn_dim_hierarchies dh
WHERE
( (q.end_date is null OR trunc(q.end_date) >= trunc(dh.start_date))
AND (dh.end_date is null OR trunc(q.start_date) <= trunc(dh.end_date)) )
AND de.dim_hierarchy_id = dh.dim_hierarchy_id
AND de.ancestor_external_id = qr.revenue_class_id
AND qr.quota_id = qa.quota_id
AND qa.comp_plan_id = p_comp_plan_id
AND qa.quota_id = q.quota_id; */
SELECT NAME,
NVL (p_rc_overlap, allow_rev_class_overlap)
INTO l_comp_plan_name,
l_rc_overlap
FROM cn_comp_plans
WHERE comp_plan_id = p_comp_plan_id;
SELECT spa.srp_plan_assign_id,
spa.salesrep_id,
spa.role_id,
r.NAME role_name,
s.NAME salesrep_name,
s.employee_number,
spa.start_date,
spa.end_date
FROM cn_srp_plan_assigns spa,
cn_salesreps s,
cn_roles r,
cn_srp_roles sr,
cn_role_plans rp
WHERE spa.comp_plan_id = p_comp_plan_id
AND spa.salesrep_id = s.salesrep_id
AND spa.role_id = r.role_id
AND sr.srp_role_id = spa.srp_role_id
AND rp.role_plan_id = spa.role_plan_id
ORDER BY s.NAME;
Select * from cn_comp_plans_all
Where comp_plan_id = p_comp_plan_id
And org_id = p_org_id;
Select * from cn_quota_assigns_all
Where comp_plan_id = p_comp_plan_id;
SELECT
DECODE (l_comp_plan.description, fnd_api.g_miss_char, NULL, l_comp_plan.description),
'INCOMPLETE',
DECODE (l_comp_plan.allow_rev_class_overlap, fnd_api.g_miss_char, NULL, l_comp_plan.allow_rev_class_overlap),
DECODE (l_comp_plan.start_date, fnd_api.g_miss_date, NULL, TRUNC (l_comp_plan.start_date)),
DECODE (l_comp_plan.end_date, fnd_api.g_miss_date, NULL, TRUNC (l_comp_plan.end_date)),
DECODE (l_comp_plan.attribute_category, fnd_api.g_miss_char, NULL, l_comp_plan.attribute_category),
DECODE (l_comp_plan.attribute1, fnd_api.g_miss_char, NULL, l_comp_plan.attribute1),
DECODE (l_comp_plan.attribute2, fnd_api.g_miss_char, NULL, l_comp_plan.attribute2),
DECODE (l_comp_plan.attribute3, fnd_api.g_miss_char, NULL, l_comp_plan.attribute3),
DECODE (l_comp_plan.attribute4, fnd_api.g_miss_char, NULL, l_comp_plan.attribute4),
DECODE (l_comp_plan.attribute5, fnd_api.g_miss_char, NULL, l_comp_plan.attribute5),
DECODE (l_comp_plan.attribute6, fnd_api.g_miss_char, NULL, l_comp_plan.attribute6),
DECODE (l_comp_plan.attribute7, fnd_api.g_miss_char, NULL, l_comp_plan.attribute7),
DECODE (l_comp_plan.attribute8, fnd_api.g_miss_char, NULL, l_comp_plan.attribute8),
DECODE (l_comp_plan.attribute9, fnd_api.g_miss_char, NULL, l_comp_plan.attribute9),
DECODE (l_comp_plan.attribute10, fnd_api.g_miss_char, NULL, l_comp_plan.attribute10),
DECODE (l_comp_plan.attribute11, fnd_api.g_miss_char, NULL, l_comp_plan.attribute11),
DECODE (l_comp_plan.attribute12, fnd_api.g_miss_char, NULL, l_comp_plan.attribute12),
DECODE (l_comp_plan.attribute13, fnd_api.g_miss_char, NULL, l_comp_plan.attribute13),
DECODE (l_comp_plan.attribute14, fnd_api.g_miss_char, NULL, l_comp_plan.attribute14),
DECODE (l_comp_plan.attribute15, fnd_api.g_miss_char, NULL, l_comp_plan.attribute15),
DECODE (l_comp_plan.org_id, fnd_api.g_miss_char, NULL, l_comp_plan.org_id)
INTO
l_comp_plan_rec.description,
l_comp_plan_rec.status_code,
l_comp_plan_rec.allow_rev_class_overlap,
l_comp_plan_rec.start_date,
l_comp_plan_rec.end_date,
l_comp_plan_rec.attribute_category,
l_comp_plan_rec.attribute1,
l_comp_plan_rec.attribute2,
l_comp_plan_rec.attribute3,
l_comp_plan_rec.attribute4,
l_comp_plan_rec.attribute5,
l_comp_plan_rec.attribute6,
l_comp_plan_rec.attribute7,
l_comp_plan_rec.attribute8,
l_comp_plan_rec.attribute9,
l_comp_plan_rec.attribute10,
l_comp_plan_rec.attribute11,
l_comp_plan_rec.attribute12,
l_comp_plan_rec.attribute13,
l_comp_plan_rec.attribute14,
l_comp_plan_rec.attribute15,
l_comp_plan_rec.org_id
FROM DUAL;