[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT name
INTO g_plan_name
FROM cn_comp_plans
WHERE comp_plan_id = x_comp_plan_id;
SELECT count(1)
INTO x_dummy
FROM cn_srp_plan_assigns
WHERE comp_plan_id = x_comp_plan_id
AND ( start_date < x_start_date
OR end_date > x_end_date);
select q.quota_id, q.start_date, q.end_date
from cn_quotas_v q,
cn_quota_assigns qa
where qa.comp_plan_id = x_comp_plan_id
and q.quota_id = qa.quota_id
and q.quota_type_code IN ('EXTERNAL', 'FORMULA');
SELECT count(de.value_external_id),
count(distinct de.value_external_id)
FROM cn_dim_explosion de,
cn_quota_rules qr,
cn_dim_hierarchies dh
WHERE
-- dh.header_dim_hierarchy_id = l_rev_class_hierarchy_id AND
l_start_date <= nvl(dh.end_date, l_start_date)
AND dh.start_date <= nvl(l_end_date, dh.start_date)
AND de.dim_hierarchy_id = dh.dim_hierarchy_id
AND de.ancestor_external_id = qr.revenue_class_id
AND qr.quota_id in (l_quota_id1, l_quota_id2);
SELECT count(1)
INTO x_dummy
FROM cn_srp_plan_assigns
WHERE comp_plan_id = x_comp_plan_id;
fnd_message.set_name('CN', 'PLN_PLAN_DELETE_NA');
SELECT count(1)
INTO x_dummy
FROM cn_role_plans
WHERE comp_plan_id = x_comp_plan_id;
SELECT lc.meaning
,lc.lookup_code
INTO X_Status
,X_status_code
FROM cn_lookups lc,
cn_comp_plans cp
WHERE cp.status_code = lc.lookup_code
AND lc.lookup_type = 'PLAN_OBJECT_STATUS'
AND cp.comp_plan_id = x_comp_plan_id
;
SELECT cn_comp_plans_s.nextval
INTO x_comp_plan_id
FROM sys.dual;
SELECT pa.salesrep_id
,pa.start_date
FROM cn_srp_plan_assigns pa
WHERE pa.comp_plan_id IN (SELECT qa.comp_plan_id
FROM cn_quota_assigns qa,
cn_quotas_v q,
cn_rt_quota_asgns rqa
WHERE qa.quota_id = q.quota_id
AND q.quota_id = rqa.quota_id
AND rqa.rate_schedule_id = X_rate_schedule_id)
ORDER BY pa.salesrep_id, pa.start_date;
SELECT pa.salesrep_id
,pa.start_date
FROM cn_srp_plan_assigns pa
WHERE pa.comp_plan_id IN (SELECT qa.comp_plan_id
FROM cn_quota_assigns qa
WHERE qa.quota_id = x_quota_id)
ORDER BY pa.salesrep_id, pa.start_date;
UPDATE cn_comp_plans
SET status_code = x_status_code
WHERE comp_plan_id = x_comp_plan_id
;
UPDATE cn_comp_plans
SET status_code = x_status_code
WHERE comp_plan_id IN (SELECT qa.comp_plan_id
FROM cn_quota_assigns qa
WHERE qa.quota_id = x_quota_id)
;
UPDATE cn_comp_plans
SET status_code = x_status_code
WHERE comp_plan_id IN (SELECT qa.comp_plan_id
FROM cn_quota_assigns qa,
cn_quotas_v q,
cn_rt_quota_asgns rqa
WHERE qa.quota_id = q.quota_id
AND q.quota_id = rqa.quota_id
AND rqa.rate_schedule_id = X_rate_schedule_id)
;
PROCEDURE insert_record
(X_Rowid IN OUT NOCOPY VARCHAR2 ,
X_Comp_Plan_Id IN OUT NOCOPY NUMBER ,
X_Name VARCHAR2 ,
X_Last_Update_Date DATE ,
X_Last_Updated_By NUMBER ,
X_Creation_Date DATE ,
X_Created_By NUMBER ,
X_Last_Update_Login NUMBER ,
X_Description VARCHAR2 ,
X_Start_Date DATE ,
X_End_Date DATE ,
x_allow_rev_class_overlap VARCHAR2 ,
x_attribute_category VARCHAR2 ,
x_attribute1 VARCHAR2 ,
x_attribute2 VARCHAR2 ,
x_attribute3 VARCHAR2 ,
x_attribute4 VARCHAR2 ,
x_attribute5 VARCHAR2 ,
x_attribute6 VARCHAR2 ,
x_attribute7 VARCHAR2 ,
x_attribute8 VARCHAR2 ,
x_attribute9 VARCHAR2 ,
x_attribute10 VARCHAR2 ,
x_attribute11 VARCHAR2 ,
x_attribute12 VARCHAR2 ,
x_attribute13 VARCHAR2 ,
x_attribute14 VARCHAR2 ,
x_attribute15 VARCHAR2 ,
x_org_id NUMBER ,
x_sum_trx_flag VARCHAR2
) IS
l_sum_trx_flag varchar2(1) := 'Y';
INSERT INTO
CN_COMP_PLANS
(
Comp_Plan_Id ,
Name ,
Last_Update_Date ,
Last_Updated_By ,
Creation_Date ,
Created_By ,
Last_Update_Login ,
Description ,
Start_date ,
End_date ,
status_code ,
allow_rev_class_overlap,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
object_version_number,
org_id,
sum_trx_flag
)
VALUES
(
X_Comp_Plan_Id ,
X_Name ,
X_Last_Update_Date ,
X_Last_Updated_By ,
X_Creation_Date ,
X_Created_By ,
X_Last_Update_Login ,
X_Description ,
X_Start_Date ,
X_End_Date ,
'INCOMPLETE' ,
x_allow_rev_class_overlap,
x_attribute_category ,
x_attribute1 ,
x_attribute2 ,
x_attribute3 ,
x_attribute4 ,
x_attribute5 ,
x_attribute6 ,
x_attribute7 ,
x_attribute8 ,
x_attribute9 ,
x_attribute10 ,
x_attribute11 ,
x_attribute12 ,
x_attribute13 ,
x_attribute14 ,
x_attribute15 ,
0,
X_org_id,
l_sum_trx_flag
);
END Insert_Record;
PROCEDURE update_record
(
X_Comp_Plan_Id IN OUT NOCOPY NUMBER ,
X_Name VARCHAR2 ,
X_Last_Update_Date DATE ,
X_Last_Updated_By NUMBER ,
X_Last_Update_Login NUMBER ,
X_Description VARCHAR2 ,
X_Start_date DATE ,
X_Start_date_old DATE ,
X_End_date DATE ,
X_End_date_old DATE ,
x_status_code VARCHAR2 ,
x_allow_rev_class_overlap VARCHAR2 ,
x_allow_rev_class_overlap_old VARCHAR2,
x_sum_trx_flag VARCHAR2,
x_attribute_category VARCHAR2 ,
x_attribute1 VARCHAR2 ,
x_attribute2 VARCHAR2 ,
x_attribute3 VARCHAR2 ,
x_attribute4 VARCHAR2 ,
x_attribute5 VARCHAR2 ,
x_attribute6 VARCHAR2 ,
x_attribute7 VARCHAR2 ,
x_attribute8 VARCHAR2 ,
x_attribute9 VARCHAR2 ,
x_attribute10 VARCHAR2 ,
x_attribute11 VARCHAR2 ,
x_attribute12 VARCHAR2 ,
x_attribute13 VARCHAR2 ,
x_attribute14 VARCHAR2 ,
x_attribute15 VARCHAR2 ) IS
BEGIN
-- Reinstate when the package is called as a batch process
-- check_unique( x_comp_plan_id,x_Name);
UPDATE cn_comp_plans
SET
Comp_Plan_Id = X_Comp_Plan_Id ,
Name = X_Name ,
Last_Update_Date = X_Last_Update_Date ,
Last_Updated_By = X_Last_Updated_By ,
Last_Update_Login = X_Last_Update_Login ,
Description = X_Description ,
Start_Date = X_Start_Date ,
End_Date = X_End_Date ,
allow_rev_class_overlap= x_allow_rev_class_overlap,
sum_trx_flag = x_sum_trx_flag,
attribute_category = x_attribute_category,
attribute1 = x_attribute1,
attribute2 = x_attribute2,
attribute3 = x_attribute3,
attribute4 = x_attribute4,
attribute5 = x_attribute5,
attribute6 = x_attribute6,
attribute7 = x_attribute7,
attribute8 = x_attribute8,
attribute9 = x_attribute9,
attribute10 = x_attribute10,
attribute11 = x_attribute11,
attribute12 = x_attribute12,
attribute13 = x_attribute13,
attribute14 = x_attribute14,
attribute15 = x_attribute15,
object_version_number = object_version_number + 1,
status_code =
decode(x_start_date , nvl(x_start_date_old,fnd_api.g_miss_date),
decode(x_end_date, nvl(x_end_date_old,fnd_api.g_miss_date),
decode(x_allow_rev_class_overlap,
nvl(x_allow_rev_class_overlap_old,'X'),
status_code, 'INCOMPLETE'
), 'INCOMPLETE'
), 'INCOMPLETE'
)
WHERE comp_plan_id = X_comp_plan_id;
END Update_Record;
SELECT * FROM CN_COMP_PLANS
WHERE comp_plan_id = X_Comp_Plan_id
FOR UPDATE OF COMP_PLAN_ID NOWAIT;
fnd_message.set_name('FND','FORM_RECORD_DELETED');
PROCEDURE delete_record(x_comp_plan_id NUMBER) IS
l_okay boolean;
select 1 into l_exists FROM cn_comp_plans
WHERE comp_plan_id = x_comp_plan_id;
(X_Operation => 'DELETE',
X_Quota_Id => null, -- delete all quotas assigned to plan
X_Comp_Plan_Id => x_comp_plan_id,
X_Quota_Assign_Id => l_junk, -- not used
X_Quota_Sequence => null, -- not used
x_quota_id_old => null,
x_org_id => NULL);-- not used
DELETE FROM cn_comp_plans
WHERE comp_plan_id = x_comp_plan_id;
END Delete_Record;
SELECT count(1)
INTO X_dummy
FROM cn_comp_plans
WHERE name = X_Name
AND (X_Comp_Plan_Id IS NULL
OR X_Comp_Plan_Id <> comp_plan_id);
,X_Last_Update_Date DATE
,X_Last_Updated_By NUMBER
,X_Creation_Date DATE
,X_Created_By NUMBER
,X_Last_Update_Login NUMBER
,X_Description VARCHAR2
,X_Start_date DATE
,X_Start_date_old DATE
,X_end_date DATE
,X_end_date_old DATE
,X_Program_Type VARCHAR2 -- not used
,x_status_code VARCHAR2
,x_allow_rev_class_overlap VARCHAR2
,x_allow_rev_class_overlap_old VARCHAR2
,x_sum_trx_flag VARCHAR2
,x_attribute_category VARCHAR2
,x_attribute1 VARCHAR2
,x_attribute2 VARCHAR2
,x_attribute3 VARCHAR2
,x_attribute4 VARCHAR2
,x_attribute5 VARCHAR2
,x_attribute6 VARCHAR2
,x_attribute7 VARCHAR2
,x_attribute8 VARCHAR2
,x_attribute9 VARCHAR2
,x_attribute10 VARCHAR2
,x_attribute11 VARCHAR2
,x_attribute12 VARCHAR2
,x_attribute13 VARCHAR2
,x_attribute14 VARCHAR2
,x_attribute15 VARCHAR2
,x_org_id NUMBER
) IS
BEGIN
IF X_Operation = 'INSERT' THEN
Insert_record ( X_Rowid ,
X_Comp_Plan_Id ,
X_Name ,
X_Last_Update_Date ,
X_Last_Updated_By ,
X_Creation_Date ,
X_Created_By ,
X_Last_Update_Login ,
X_Description ,
X_start_date ,
X_end_date ,
x_allow_rev_class_overlap,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5 ,
x_attribute6 ,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11 ,
x_attribute12 ,
x_attribute13 ,
x_attribute14 ,
x_attribute15,
x_org_id,
x_sum_trx_flag
);
ELSIF X_Operation = 'UPDATE' THEN
Update_record (X_Comp_Plan_Id ,
X_Name ,
X_Last_Update_Date ,
X_Last_Updated_By ,
X_Last_Update_Login ,
X_Description ,
X_Start_date ,
X_Start_date_old ,
X_End_date ,
X_End_date_old ,
x_status_code ,
x_allow_rev_class_overlap,
x_allow_rev_class_overlap_old,
x_sum_trx_flag,
x_attribute_category,
x_attribute1 ,
x_attribute2 ,
x_attribute3 ,
x_attribute4 ,
x_attribute5 ,
x_attribute6 ,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11 ,
x_attribute12 ,
x_attribute13 ,
x_attribute14 ,
x_attribute15 );
ELSIF X_Operation = 'DELETE' THEN
Delete_Record ( X_Comp_Plan_Id);
SELECT qa.quota_id
,q.quota_type_code
,q.name
FROM cn_quota_assigns qa,
cn_quotas_v q
WHERE qa.comp_plan_id = x_comp_plan_id
AND qa.quota_id = q.quota_id;