The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pay_group_id
FROM cn_pay_groups
WHERE name = p_PayGroup_rec.name
AND start_date = p_PayGroup_rec.start_date
AND end_date = p_PayGroup_rec.end_date
and org_id= p_PayGroup_rec.org_id;
SELECT cn_pay_groups_s.nextval
INTO x_pay_group_id
FROM dual;
L_LAST_UPDATE_DATE DATE := sysdate;
L_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
L_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
SELECT period_set_id
FROM cn_period_sets
WHERE period_set_name = p_paygroup_rec.period_set_name
and org_id = p_paygroup_rec.org_id;
SELECT period_type_id
FROM cn_period_types
WHERE period_type = p_paygroup_rec.period_type
and org_id = p_paygroup_rec.org_id;
x_loading_status := 'CN_INSERTED';
SELECT COUNT(*)
INTO l_count
FROM cn_pay_groups
WHERE name = p_PayGroup_rec.name
AND start_date = p_PayGroup_rec.start_date
AND end_date = p_PayGroup_rec.end_date
and org_id = p_PayGroup_rec.org_id;
SELECT 1 INTO l_dummy FROM dual
WHERE NOT EXISTS
( SELECT 1
FROM cn_pay_groups
WHERE
((end_date IS NOT NULL) AND
(p_paygroup_rec.end_date IS NOT NULL) AND
((start_date BETWEEN p_paygroup_rec.start_date
AND p_Paygroup_rec.end_date) OR
(end_date BETWEEN p_Paygroup_rec.start_date
AND p_paygroup_rec.end_date) OR
(p_paygroup_rec.start_date BETWEEN start_date
AND end_date))
)
AND name = p_paygroup_rec.name
and org_id = p_paygroup_rec.org_id
);
x_operation => 'INSERT',
x_rowid => L_ROWID,
x_pay_group_id => l_pay_group_id,
x_name => p_PayGroup_rec.name,
x_period_set_name => p_PayGroup_rec.period_set_name,
x_period_type => p_PayGroup_rec.period_type,
x_start_date => p_PayGroup_rec.start_date,
x_end_date => p_PayGroup_rec.end_date,
x_pay_group_description=> p_PayGroup_rec.pay_group_description,
x_period_set_id => l_period_set_id,
x_period_type_id => l_period_type_id,
x_attribute_category => p_PayGroup_rec.attribute_category,
x_attribute1 => p_PayGroup_rec.attribute1,
x_attribute2 => p_PayGroup_rec.attribute2,
x_attribute3 => p_PayGroup_rec.attribute3,
x_attribute4 => p_PayGroup_rec.attribute4,
x_attribute5 => p_PayGroup_rec.attribute5,
x_attribute6 => p_PayGroup_rec.attribute6,
x_attribute7 => p_PayGroup_rec.attribute7,
x_attribute8 => p_PayGroup_rec.attribute8,
x_attribute9 => p_PayGroup_rec.attribute9,
x_attribute10 => p_PayGroup_rec.attribute10,
x_attribute11 => p_PayGroup_rec.attribute10,
x_attribute12 => p_PayGroup_rec.attribute12,
x_attribute13 => p_PayGroup_rec.attribute13,
x_attribute14 => p_PayGroup_rec.attribute14,
x_attribute15 => p_PayGroup_rec.attribute15,
x_last_update_date => l_last_update_date,
x_last_updated_by => l_last_updated_by,
x_creation_date => l_creation_date,
x_created_by => l_created_by,
x_last_update_login => l_last_update_login,
x_program_type => l_program_type,
x_object_version_number => p_PayGroup_rec.object_version_number,
x_org_id => p_PayGroup_rec.org_id
);
PROCEDURE Update_PayGroup (
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_PayGroup_rec IN OUT NOCOPY PayGroup_rec_type,
x_status OUT NOCOPY VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2
) IS
L_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PayGroup_PUB';
L_LAST_UPDATE_DATE DATE := sysdate;
L_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
L_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_PayGroup';
SELECT period_set_id
FROM cn_period_sets
WHERE period_set_name = p_paygroup_rec.period_set_name
and org_id = p_paygroup_rec.org_id;
SELECT period_type_id
FROM cn_period_types
WHERE period_type = p_paygroup_rec.period_type
and org_id = p_paygroup_rec.org_id;
SELECT *
FROM cn_pay_groups
WHERE pay_group_id = p_pay_group_id;
select
pay_group_id,
name,
period_set_name,
period_type,
start_date,
end_date,
pay_group_description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
object_version_number,
org_id
from cn_pay_groups
where pay_group_id = p_paygroup_rec.pay_group_id;
SELECT salesrep_id
FROM cn_srp_pay_groups
WHERE pay_group_id = c_pay_group_id
AND trunc(start_date) = trunc(c_start_date)
AND trunc(nvl(end_date, l_null_date)) = trunc(nvl(c_end_date, l_null_date))
AND org_id = c_org_id;
SELECT role_id
FROM cn_srp_roles
WHERE salesrep_id = p_salesrep_id
and org_id = p_org_id;
SELECT comp_plan_id,
start_date,
end_date
FROM cn_srp_plan_assigns
WHERE role_id = p_role_id
AND salesrep_id = p_salesrep_id
AND org_id = p_org_id;
select sr.srp_role_id, rp.role_plan_id
from cn_srp_pay_groups spg, cn_pay_groups pg,
cn_srp_roles sr, cn_role_plans rp
where spg.end_date is null
and spg.pay_group_id = pg.pay_group_id
and pg.pay_group_id = p_paygroup_rec.pay_group_id
and sr.salesrep_id = spg.salesrep_id
and sr.org_id = spg.org_id
and sr.role_id = rp.role_id
and greatest(sr.start_date, rp.start_date) <=
least(nvl(sr.end_date, l_null_date),
nvl(rp.end_date, l_null_date));
SAVEPOINT Update_PayGroup;
x_loading_status := 'CN_UPDATED';
IF p_paygroup_rec.start_date IS NOT NULL --start date has been updated
THEN
IF p_paygroup_rec.end_date IS NOT NULL
AND (p_paygroup_rec.start_date > p_paygroup_rec.end_date)
THEN
--Error condition
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
THEN
fnd_message.set_name('CN', 'CN_INVALID_DATE_RANGE');
SELECT COUNT(*)
INTO l_count
FROM cn_pay_groups
WHERE name = p_PayGroup_rec.name
AND start_date = p_PayGroup_rec.start_date
AND end_date = p_PayGroup_rec.end_date
and org_id = p_PayGroup_rec.org_id
AND pay_group_id <> l_pay_group_id;
SELECT 1 INTO l_dummy FROM dual
WHERE NOT EXISTS
( SELECT 1
FROM cn_pay_groups
WHERE
((end_date IS NOT NULL) AND
(p_paygroup_rec.end_date IS NOT NULL) AND
((start_date BETWEEN p_paygroup_rec.start_date
AND p_Paygroup_rec.end_date) OR
(end_date BETWEEN p_Paygroup_rec.start_date
AND p_paygroup_rec.end_date) OR
(p_paygroup_rec.start_date BETWEEN start_date
AND end_date))
)
AND name = p_paygroup_rec.name
and org_id = p_paygroup_rec.org_id
AND pay_group_id <> l_pay_group_id
);
SELECT COUNT(1)
INTO l_count
FROM cn_srp_pay_groups
WHERE pay_group_id = l_pay_group_id;
SELECT MIN(start_date),MAX(end_date)
INTO l_start_date,l_end_date
FROM cn_srp_pay_groups
WHERE pay_group_id = l_pay_group_id;
SELECT COUNT(1)
INTO l_count
FROM cn_role_pay_groups
WHERE pay_group_id = l_pay_group_id;
SELECT MIN(start_date)
INTO l_start_date
FROM cn_role_pay_groups
WHERE pay_group_id = l_pay_group_id;
SELECT MAX(end_date)
INTO l_end_date
FROM cn_role_pay_groups
WHERE pay_group_id = l_pay_group_id;
select 1 into l_valid_data from dual where exists
(select count(cp.pay_date) from
cn_srp_pay_groups cspg,cn_payment_worksheets cpw,cn_payruns cp
where
cp.payrun_id = cpw.payrun_id and cp.pay_group_id = cspg.pay_group_id and
cp.org_id = cpw.org_id and cp.org_id = cspg.org_id and
cpw.salesrep_id = cspg.salesrep_id and cpw.org_id = cspg.org_id and
cspg.pay_group_id=p_PayGroup_rec.pay_group_id and cpw.quota_id is null);
select max(cps.end_date) into l_pay_period_end_date from
cn_srp_pay_groups cspg,cn_payment_worksheets cpw,cn_payruns cp,cn_period_statuses cps
where
cp.payrun_id = cpw.payrun_id and cp.pay_group_id = cspg.pay_group_id and
cp.org_id = cpw.org_id and cp.org_id = cspg.org_id and
cpw.salesrep_id = cspg.salesrep_id and cpw.org_id = cspg.org_id and
cspg.pay_group_id=p_PayGroup_rec.pay_group_id and cpw.quota_id is null
and cp.pay_period_id = cps.period_id and cp.org_id = cps.org_id;
SELECT 1 INTO l_dummy FROM dual
WHERE NOT EXISTS
( SELECT 1
FROM cn_pay_groups pg,
cn_payruns p
WHERE pg.pay_group_id = p.pay_group_id
and pg.org_id = p.org_id
AND pg.pay_group_id = l_pay_group_id
);
SELECT 1 INTO l_dummy FROM dual
WHERE NOT EXISTS
( SELECT 1
FROM cn_srp_periods csp,
cn_posting_details_sum cpd,
cn_srp_pay_groups spg
WHERE cpd.credited_salesrep_id = spg.salesrep_id
and cpd.pay_period_id = csp.period_id
and csp.salesrep_id = cpd.credited_salesrep_id
and csp.org_id = cpd.org_id
and csp.org_id = spg.org_id
and csp.start_date between spg.start_date and nvl(spg.end_date, csp.end_date)
AND spg.pay_group_id = l_pay_group_id
);
x_operation => 'UPDATE',
x_rowid => L_ROWID,
x_pay_group_id => l_pay_group_id,
x_name => p_PayGroup_rec.name,
x_period_set_name => p_PayGroup_rec.period_set_name,
x_period_type => p_PayGroup_rec.period_type,
x_start_date => p_PayGroup_rec.start_date,
x_end_date => p_PayGroup_rec.end_date,
x_pay_group_description=> p_PayGroup_rec.pay_group_description,
x_period_set_id => l_period_set_id,
x_period_type_id => l_period_type_id,
x_attribute_category => p_PayGroup_rec.attribute_category,
x_attribute1 => p_PayGroup_rec.attribute1,
x_attribute2 => p_PayGroup_rec.attribute2,
x_attribute3 => p_PayGroup_rec.attribute3,
x_attribute4 => p_PayGroup_rec.attribute4,
x_attribute5 => p_PayGroup_rec.attribute5,
x_attribute6 => p_PayGroup_rec.attribute6,
x_attribute7 => p_PayGroup_rec.attribute7,
x_attribute8 => p_PayGroup_rec.attribute8,
x_attribute9 => p_PayGroup_rec.attribute9,
x_attribute10 => p_PayGroup_rec.attribute10,
x_attribute11 => p_PayGroup_rec.attribute10,
x_attribute12 => p_PayGroup_rec.attribute12,
x_attribute13 => p_PayGroup_rec.attribute13,
x_attribute14 => p_PayGroup_rec.attribute14,
x_attribute15 => p_PayGroup_rec.attribute15,
x_last_update_date => l_last_update_date,
x_last_updated_by => l_last_updated_by,
x_creation_date => l_creation_date,
x_created_by => l_created_by,
x_last_update_login => l_last_update_login,
x_program_type => l_program_type,
x_object_version_number => L_OBJECT_VERSION_NUMBER,
x_org_id => p_PayGroup_rec.org_id
);
cn_srp_plan_assigns_pvt.update_srp_plan_assigns
(p_api_version => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_srp_role_id => s.srp_role_id,
p_role_plan_id => s.role_plan_id,
p_attribute_rec => NULL,
x_loading_status => x_loading_status);
ROLLBACK TO Update_PayGroup;
ROLLBACK TO Update_PayGroup;
ROLLBACK TO Update_PayGroup;
END Update_PayGroup;
PROCEDURE Delete_PayGroup
( 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_PayGroup_rec IN OUT NOCOPY PayGroup_rec_type ,
x_status OUT NOCOPY VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2
) IS
L_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PayGroup_PUB';
L_LAST_UPDATE_DATE DATE := sysdate;
L_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
L_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
:= 'Delete_PayGroup';
SAVEPOINT Delete_PayGroup ;
x_loading_status := 'CN_DELETED';
SELECT COUNT(1)
INTO l_count_role
FROM cn_role_pay_groups
WHERE pay_group_id = l_pay_group_id;
SELECT COUNT(1)
INTO l_count
FROM cn_srp_pay_groups
WHERE pay_group_id = l_pay_group_id;
x_operation => 'DELETE',
x_rowid => L_ROWID,
x_pay_group_id => l_pay_group_id,
x_name => null,
x_period_set_name => null,
x_period_type => null,
x_start_date => null,
x_end_date => null,
x_pay_group_description=> null,
x_period_set_id => NULL,
x_period_type_id => 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_last_update_date => null,
x_last_updated_by => l_last_updated_by,
x_creation_date => l_creation_date,
x_created_by => l_created_by,
x_last_update_login => l_last_update_login,
x_program_type => l_program_type,
x_object_version_number => L_OBJECT_VERSION_NUMBER,
x_org_id => null
);
ROLLBACK TO Delete_PayGroup;
ROLLBACK TO Delete_PayGroup;
ROLLBACK TO Delete_PayGroup;
END Delete_PayGroup;