The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cn_srp_plan_assigns_s.nextval
INTO x_srp_plan_assign_id
FROM dual;
SELECT MAX(end_date)
INTO l_next_end_date
FROM cn_period_statuses_all s, cn_repositories_all r
WHERE s.period_type_id = r.period_type_id
AND s.period_set_id = r.period_set_id
AND s.org_id = p_org_id
AND r.org_id = p_org_id;
SELECT MIN(end_date)
INTO l_next_end_date
FROM cn_period_statuses_all s, cn_repositories_all r
WHERE trunc(end_date) >= trunc(p_end_date)
AND s.period_type_id = r.period_type_id
AND s.period_set_id = r.period_set_id
AND s.org_id = p_org_id
AND r.org_id = p_org_id;
PROCEDURE INSERT_ROW
(X_SRP_PLAN_ASSIGN_ID IN OUT NOCOPY NUMBER,
X_SRP_ROLE_ID IN NUMBER,
X_ROLE_PLAN_ID IN NUMBER,
X_SALESREP_ID IN NUMBER,
X_ROLE_ID IN NUMBER,
X_COMP_PLAN_ID IN NUMBER,
X_START_DATE IN DATE,
X_END_DATE IN DATE,
X_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_ATTRIBUTE1 IN VARCHAR2,
X_ATTRIBUTE2 IN VARCHAR2,
X_ATTRIBUTE3 IN VARCHAR2,
X_ATTRIBUTE4 IN VARCHAR2,
X_ATTRIBUTE5 IN VARCHAR2,
X_ATTRIBUTE6 IN VARCHAR2,
X_ATTRIBUTE7 IN VARCHAR2,
X_ATTRIBUTE8 IN VARCHAR2,
X_ATTRIBUTE9 IN VARCHAR2,
X_ATTRIBUTE10 IN VARCHAR2,
X_ATTRIBUTE11 IN VARCHAR2,
X_ATTRIBUTE12 IN VARCHAR2,
X_ATTRIBUTE13 IN VARCHAR2,
X_ATTRIBUTE14 IN VARCHAR2,
X_ATTRIBUTE15 IN VARCHAR2,
X_CREATED_BY IN NUMBER,
X_CREATION_DATE IN DATE,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER
) IS
l_dummy NUMBER;
SELECT org_id INTO l_org_id
FROM cn_comp_plans_all
WHERE comp_plan_id = x_comp_plan_id;
INSERT INTO CN_SRP_PLAN_ASSIGNS
(SRP_PLAN_ASSIGN_ID,
SRP_ROLE_ID,
ROLE_PLAN_ID,
SALESREP_ID,
ORG_ID,
ROLE_ID,
COMP_PLAN_ID,
START_DATE,
END_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) VALUES
(X_SRP_PLAN_ASSIGN_ID,
X_SRP_ROLE_ID,
X_ROLE_PLAN_ID,
X_SALESREP_ID,
l_ORG_ID,
X_ROLE_ID,
X_COMP_PLAN_ID,
X_START_DATE,
X_END_DATE,
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_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
SELECT 1 INTO l_dummy FROM CN_SRP_PLAN_ASSIGNS_ALL
WHERE SRP_PLAN_ASSIGN_ID = X_SRP_PLAN_ASSIGN_ID;
END INSERT_ROW;
SELECT * FROM CN_SRP_PLAN_ASSIGNS_ALL
WHERE SRP_PLAN_ASSIGN_ID = X_SRP_PLAN_ASSIGN_ID
FOR UPDATE OF SRP_PLAN_ASSIGN_ID nowait;
fnd_message.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE UPDATE_ROW
(X_SRP_PLAN_ASSIGN_ID IN NUMBER,
X_SRP_ROLE_ID IN NUMBER,
X_ROLE_PLAN_ID IN NUMBER,
X_SALESREP_ID IN NUMBER,
X_ROLE_ID IN NUMBER,
X_COMP_PLAN_ID IN NUMBER,
X_START_DATE IN DATE,
X_END_DATE IN DATE,
X_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_ATTRIBUTE1 IN VARCHAR2,
X_ATTRIBUTE2 IN VARCHAR2,
X_ATTRIBUTE3 IN VARCHAR2,
X_ATTRIBUTE4 IN VARCHAR2,
X_ATTRIBUTE5 IN VARCHAR2,
X_ATTRIBUTE6 IN VARCHAR2,
X_ATTRIBUTE7 IN VARCHAR2,
X_ATTRIBUTE8 IN VARCHAR2,
X_ATTRIBUTE9 IN VARCHAR2,
X_ATTRIBUTE10 IN VARCHAR2,
X_ATTRIBUTE11 IN VARCHAR2,
X_ATTRIBUTE12 IN VARCHAR2,
X_ATTRIBUTE13 IN VARCHAR2,
X_ATTRIBUTE14 IN VARCHAR2,
X_ATTRIBUTE15 IN VARCHAR2,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER
) IS
l_srp_role_id CN_SRP_PLAN_ASSIGNS.srp_role_id%TYPE;
SELECT * FROM CN_SRP_PLAN_ASSIGNS_ALL
WHERE SRP_PLAN_ASSIGN_ID = X_SRP_PLAN_ASSIGN_ID
FOR UPDATE OF SRP_PLAN_ASSIGN_ID nowait;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
SELECT
decode(x_srp_role_id,
fnd_api.g_miss_num, oldrow.srp_role_id,
x_srp_role_id),
decode(x_role_plan_id,
fnd_api.g_miss_num, oldrow.role_plan_id,
x_role_plan_id),
decode(x_salesrep_id,
fnd_api.g_miss_num, oldrow.salesrep_id,
x_salesrep_id),
decode(x_role_id,
fnd_api.g_miss_num, oldrow.role_id,
x_role_id),
decode(x_comp_plan_id,
fnd_api.g_miss_num, oldrow.comp_plan_id,
x_comp_plan_id),
decode(x_start_date,
fnd_api.g_miss_date, oldrow.start_date,
x_start_date),
decode(x_end_date,
fnd_api.g_miss_date, oldrow.end_date,
x_end_date),
decode(x_attribute_category,
fnd_api.g_miss_char, oldrow.attribute_category,
x_attribute_category),
decode(x_attribute1,
fnd_api.g_miss_char, oldrow.attribute1,
x_attribute1),
decode(x_attribute2,
fnd_api.g_miss_char, oldrow.attribute2,
x_attribute2),
decode(x_attribute3,
fnd_api.g_miss_char, oldrow.attribute3,
x_attribute3),
decode(x_attribute4,
fnd_api.g_miss_char, oldrow.attribute4,
x_attribute4),
decode(x_attribute5,
fnd_api.g_miss_char, oldrow.attribute5,
x_attribute5),
decode(x_attribute6,
fnd_api.g_miss_char, oldrow.attribute6,
x_attribute6),
decode(x_attribute7,
fnd_api.g_miss_char, oldrow.attribute7,
x_attribute7),
decode(x_attribute8,
fnd_api.g_miss_char, oldrow.attribute8,
x_attribute8),
decode(x_attribute9,
fnd_api.g_miss_char, oldrow.attribute9,
x_attribute9),
decode(x_attribute10,
fnd_api.g_miss_char, oldrow.attribute10,
x_attribute10),
decode(x_attribute11,
fnd_api.g_miss_char, oldrow.attribute11,
x_attribute11),
decode(x_attribute12,
fnd_api.g_miss_char, oldrow.attribute12,
x_attribute12),
decode(x_attribute13,
fnd_api.g_miss_char, oldrow.attribute13,
x_attribute13),
decode(x_attribute14,
fnd_api.g_miss_char, oldrow.attribute14,
x_attribute14),
decode(x_attribute15,
fnd_api.g_miss_char, oldrow.attribute15,
x_attribute15)
INTO
l_srp_role_id,
l_role_plan_id,
l_salesrep_id,
l_role_id,
l_comp_plan_id,
l_start_date,
l_end_date,
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
FROM dual;
UPDATE CN_SRP_PLAN_ASSIGNS_ALL SET
SRP_PLAN_ASSIGN_ID = x_srp_plan_assign_id ,
SRP_ROLE_ID = l_srp_role_id ,
ROLE_PLAN_ID = l_role_plan_id ,
SALESREP_ID = l_salesrep_id ,
ROLE_ID = l_role_id ,
COMP_PLAN_ID = l_comp_plan_id ,
START_DATE = l_start_date ,
END_DATE = l_end_date ,
ATTRIBUTE_CATEGORY = l_attribute_category ,
ATTRIBUTE1 = l_attribute1 ,
ATTRIBUTE2 = l_attribute2 ,
ATTRIBUTE3 = l_attribute3 ,
ATTRIBUTE4 = l_attribute4 ,
ATTRIBUTE5 = l_attribute5 ,
ATTRIBUTE6 = l_attribute6 ,
ATTRIBUTE7 = l_attribute7 ,
ATTRIBUTE8 = l_attribute8 ,
ATTRIBUTE9 = l_attribute9 ,
ATTRIBUTE10 = l_attribute10 ,
ATTRIBUTE11 = l_attribute11 ,
ATTRIBUTE12 = l_attribute12 ,
ATTRIBUTE13 = l_attribute13 ,
ATTRIBUTE14 = l_attribute14 ,
ATTRIBUTE15 = l_attribute15 ,
LAST_UPDATE_DATE = x_last_update_date ,
LAST_UPDATED_BY = x_last_updated_by ,
LAST_UPDATE_LOGIN = x_last_update_login
WHERE SRP_PLAN_ASSIGN_ID = x_srp_plan_assign_id;
cn_srp_period_quotas_pkg.insert_record
(x_srp_plan_assign_id => x_srp_plan_assign_id
,x_quota_id => NULL
,x_start_period_id => NULL -- obsolete
,x_end_period_id => NULL -- obsolete
,x_start_date => next_period(oldrow.end_date,
oldrow.org_id)
,x_end_date => x_end_date );
cn_srp_per_quota_rc_pkg.insert_record
(x_srp_plan_assign_id => x_srp_plan_assign_id
,x_quota_id => NULL
,x_revenue_class_id => NULL
,x_start_period_id => NULL
,x_end_period_id => NULL
,x_start_date => next_period(oldrow.end_date,
oldrow.org_id)
,x_end_date => x_end_date);
cn_srp_period_quotas_pkg.delete_record
( x_srp_plan_assign_id => x_srp_plan_assign_id
,x_quota_id => NULL
,x_start_period_id => NULL -- obsolete
,x_end_period_id => NULL -- obsolete
,x_start_date => next_period(x_end_date,
oldrow.org_id)
,x_end_date => oldrow.end_date );
cn_srp_per_quota_rc_pkg.delete_record
(x_srp_plan_assign_id => x_srp_plan_assign_id
,x_quota_id => NULL
,x_revenue_class_id => NULL
,x_start_period_id => NULL
,x_end_period_id => NULL
,x_start_date => next_period(x_end_date,
oldrow.org_id)
,x_end_date => oldrow.end_date );
SELECT MIN(start_date)
INTO l_next_start_date
FROM cn_acc_period_statuses_v
WHERE period_status IN ('F', 'O')
AND org_id = oldrow.org_id;
cn_srp_period_quotas_pkg.insert_record
(x_srp_plan_assign_id => x_srp_plan_assign_id
,x_quota_id => NULL
,x_start_period_id => NULL -- obsolete
,x_end_period_id => NULL -- obsolete
,x_start_date => next_period(oldrow.end_date,
oldrow.org_id)
,x_end_date => x_end_date );
cn_srp_per_quota_rc_pkg.insert_record
(x_srp_plan_assign_id => x_srp_plan_assign_id
,x_quota_id => NULL
,x_revenue_class_id => NULL
,x_start_period_id => NULL
,x_end_period_id => NULL
,x_start_date => next_period(oldrow.end_date,
oldrow.org_id)
,x_end_date => x_end_date);
cn_srp_period_quotas_pkg.delete_record
( x_srp_plan_assign_id => x_srp_plan_assign_id
,x_quota_id => NULL
,x_start_period_id => NULL -- obsolete
,x_end_period_id => NULL -- obsolete
,x_start_date => next_period(x_end_date,
oldrow.org_id)
,x_end_date => oldrow.end_date );
cn_srp_per_quota_rc_pkg.delete_record
(x_srp_plan_assign_id => x_srp_plan_assign_id
,x_quota_id => NULL
,x_revenue_class_id => NULL
,x_start_period_id => NULL
,x_end_period_id => NULL
,x_start_date => next_period(x_end_date,
oldrow.org_id)
,x_end_date => oldrow.end_date );
-- start_date changed, delete/add the whole set
-- Remove all assignments for this plan/salesrep
-- cascades to per_quota_rc
cn_srp_per_quota_rc_pkg.delete_record
(x_srp_plan_assign_id => x_srp_plan_assign_id
,x_quota_id => NULL
,x_revenue_class_id => NULL
,x_start_period_id => NULL
,x_end_period_id => NULL
,x_start_date => oldrow.start_date
,x_end_date => oldrow.end_date );
cn_srp_period_quotas_pkg.delete_record
( x_srp_plan_assign_id => x_srp_plan_assign_id
,x_quota_id => NULL
,x_start_period_id => NULL -- obsolete
,x_end_period_id => NULL -- obsolete
,x_start_date => oldrow.start_date
,x_end_date => oldrow.end_date );
cn_srp_period_quotas_pkg.insert_record
(x_srp_plan_assign_id => x_srp_plan_assign_id
,x_quota_id => NULL
,x_start_period_id => NULL -- obsolete
,x_end_period_id => NULL -- obsolete
,x_start_date => x_start_date
,x_end_date => x_end_date );
cn_srp_per_quota_rc_pkg.insert_record
(x_srp_plan_assign_id => x_srp_plan_assign_id
,x_quota_id => NULL
,x_revenue_class_id => NULL
,x_start_period_id => NULL
,x_end_period_id => NULL
,x_start_date => x_start_date
,x_end_date => x_end_date);
END UPDATE_ROW;
PROCEDURE DELETE_ROW (X_SRP_PLAN_ASSIGN_ID IN NUMBER) IS
BEGIN
-- delete child rec is called in API:CN_SRP_PLAN_ASSIGNS_PVT
-- cn_srp_quota_assigns_pkg.delete_record
DELETE FROM CN_SRP_PLAN_ASSIGNS_ALL
WHERE SRP_PLAN_ASSIGN_ID = X_SRP_PLAN_ASSIGN_ID;
END DELETE_ROW;