[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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,
cn_quotas_v q,
cn_dim_hierarchies dh
WHERE dh.header_dim_hierarchy_id = l_rev_class_hierarchy_id
AND ( ( q.end_date IS NULL
AND NVL (TRUNC (dh.end_date), TRUNC (q.start_date)) >=
TRUNC (q.start_date)
)
OR ( q.end_date IS NOT NULL
AND ( TRUNC (dh.start_date) BETWEEN TRUNC (q.start_date)
AND TRUNC (q.end_date)
OR ( TRUNC (dh.start_date) < TRUNC (q.start_date)
AND NVL (TRUNC (dh.end_date),
TRUNC (q.end_date))
BETWEEN TRUNC (q.start_date)
AND TRUNC (q.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 = l_cp_id
AND qa.quota_id = q.quota_id
-- only formula and external have revenue classes
-- Modified
AND q.quota_type_code IN ('EXTERNAL', 'FORMULA');
SELECT comp_plan_id, allow_rev_class_overlap
INTO x_cp_id, l_rc_overlap
FROM cn_comp_plans
WHERE NAME = p_cp_name AND org_id = p_org_id;
SELECT quota_id, quota_type_code, start_date,
end_date
INTO x_pe_id, l_quota_type_code, l_quota_start_date,
l_quota_end_date
FROM cn_quotas_v
WHERE NAME = p_pe_name AND org_id = p_org_id ;
SELECT 1
INTO l_dummy
FROM SYS.DUAL
WHERE NOT EXISTS (
SELECT 1
FROM cn_quota_assigns
WHERE quota_id = x_pe_id
AND comp_plan_id = x_cp_id);
l_loading_status := 'CN_INSERTED';
(x_operation => 'INSERT',
x_comp_plan_id => l_comp_plan_id,
x_quota_id => l_pe_id,
x_quota_assign_id => l_quota_assign_id,
x_quota_id_old => NULL,
x_quota_sequence => 0,
x_org_id => p_org_id
);
SELECT NAME, description, start_date, end_date, status_code,
allow_rev_class_overlap
FROM cn_comp_plans
WHERE NAME = p_cp_rec.NAME
AND org_id = p_cp_rec.org_id;
SELECT 'CP_EXIST'
INTO x_loading_status
FROM cn_comp_plans
WHERE NAME = p_cp_rec.NAME
AND org_id = p_cp_rec.org_id;
x_loading_status := 'CN_INSERTED';
SELECT DECODE (l_p_comp_plan_rec.NAME,
fnd_api.g_miss_char, NULL,
LTRIM (RTRIM (l_p_comp_plan_rec.NAME))
)
INTO l_cp_rec.NAME
FROM SYS.DUAL;
SELECT DECODE (l_p_comp_plan_rec.start_date,
fnd_api.g_miss_date, NULL,
l_p_comp_plan_rec.start_date
)
INTO l_cp_rec.start_date
FROM SYS.DUAL;
SELECT DECODE (l_p_comp_plan_rec.end_date,
fnd_api.g_miss_date, NULL,
l_p_comp_plan_rec.end_date
)
INTO l_cp_rec.end_date
FROM SYS.DUAL;
SELECT DECODE (l_p_comp_plan_rec.description,
fnd_api.g_miss_char, NULL,
LTRIM (RTRIM (l_p_comp_plan_rec.description))
)
INTO l_cp_rec.description
FROM SYS.DUAL;
SELECT DECODE (l_p_comp_plan_rec.plan_element_name,
fnd_api.g_miss_char, NULL,
LTRIM (RTRIM (l_p_comp_plan_rec.plan_element_name))
)
INTO l_cp_rec.plan_element_name
FROM SYS.DUAL;
SELECT DECODE (l_p_comp_plan_rec.org_id,
fnd_api.g_miss_char, NULL,
LTRIM (RTRIM (l_p_comp_plan_rec.org_id))
)
INTO l_cp_rec.org_id
FROM SYS.DUAL;
/* SELECT lookup_code INTO l_cp_rec.status
FROM cn_lookups
WHERE lookup_type = 'PLAN_OBJECT_STATUS'
AND Upper(meaning) = 'INCOMPLETE'; */
SELECT DECODE (l_p_comp_plan_rec.rc_overlap,
fnd_api.g_miss_char, 'No',
LTRIM (RTRIM (l_p_comp_plan_rec.rc_overlap))
)
INTO l_lk_meaning
FROM SYS.DUAL;
SELECT lookup_code INTO l_cp_rec.rc_overlap
FROM fnd_lookups
WHERE lookup_type = 'YES_NO'
AND Upper(meaning) = Upper(l_lk_meaning);
SELECT DECODE (l_p_comp_plan_rec.rc_overlap,
fnd_api.g_miss_char, 'N',
NULL, 'N',
LTRIM (RTRIM (l_p_comp_plan_rec.rc_overlap))
)
INTO l_cp_rec.rc_overlap
FROM SYS.DUAL;
(x_operation => 'INSERT',
x_rowid => g_rowid,
x_comp_plan_id => x_comp_plan_id,
x_name => l_cp_rec.NAME,
x_description => l_cp_rec.description,
x_start_date => l_cp_rec.start_date,
x_end_date => l_cp_rec.end_date,
x_status_code => l_cp_rec.status,
x_allow_rev_class_overlap => l_cp_rec.rc_overlap,
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_global.user_id,
x_creation_date => SYSDATE,
x_created_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id,
x_program_type => g_program_type,
x_start_date_old => NULL,
x_end_date_old => NULL,
x_allow_rev_class_overlap_old => NULL,
x_attribute_category => l_p_comp_plan_rec.attribute_category,
x_attribute1 => l_p_comp_plan_rec.attribute1,
x_attribute2 => l_p_comp_plan_rec.attribute2,
x_attribute3 => l_p_comp_plan_rec.attribute3,
x_attribute4 => l_p_comp_plan_rec.attribute4,
x_attribute5 => l_p_comp_plan_rec.attribute5,
x_attribute6 => l_p_comp_plan_rec.attribute6,
x_attribute7 => l_p_comp_plan_rec.attribute7,
x_attribute8 => l_p_comp_plan_rec.attribute8,
x_attribute9 => l_p_comp_plan_rec.attribute9,
x_attribute10 => l_p_comp_plan_rec.attribute10,
x_attribute11 => l_p_comp_plan_rec.attribute11,
x_attribute12 => l_p_comp_plan_rec.attribute12,
x_attribute13 => l_p_comp_plan_rec.attribute13,
x_attribute14 => l_p_comp_plan_rec.attribute14,
x_attribute15 => l_p_comp_plan_rec.attribute15,
x_org_id => l_p_comp_plan_rec.org_id
);
SELECT comp_plan_id, status_code
INTO x_comp_plan_id, l_status_code
FROM cn_comp_plans
WHERE NAME = l_cp_rec.NAME
AND org_id = l_cp_rec.org_id;
/* 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
);