[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select org_id, pay_group_id, start_date, end_date
into l_org_id, l_pay_group_id, l_role_pg_start_date, l_role_pg_end_date
from cn_role_pay_groups
where role_pay_group_id = p_role_pay_group_id;
select start_date, end_date, salesrep_id
into l_srp_role_start_date, l_srp_role_end_date, l_salesrep_id
from cn_srp_roles
where srp_role_id = p_srp_role_id
and org_id = l_org_id;
select start_date_active, end_date_active
into l_res_start_date, l_res_end_date
from cn_salesreps
where salesrep_id = l_salesrep_id
and org_id = l_org_id;
select start_date, end_date
into l_pg_start_date, l_pg_end_date
from cn_pay_groups
where pay_group_id = l_pay_group_id;
SELECT salesrep_id
INTO l_salesrep_id
FROM cn_salesreps
WHERE employee_number IS NULL
AND source_id = p_source_id
AND org_id = p_org_id
AND ((type = p_type) OR (type IS NULL AND p_type IS NULL));
SELECT /*+ first_rows */ salesrep_id
INTO l_salesrep_id
FROM cn_salesreps
WHERE upper(employee_number) = l_emp_num
AND source_id = p_source_id
AND org_id = p_org_id
AND ((type = p_type) OR (type IS NULL AND p_type IS NULL));
SELECT pay_group_id
FROM cn_pay_groups_all
WHERE name = p_paygroup_assign_rec.pay_group_name
AND org_id = l_org_id;
x_loading_status := 'CN_INSERTED';
x_loading_status := 'CN_INSERTED';
PROCEDURE Update_srp_assignment
( 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_old_paygroup_assign_rec IN PayGroup_assign_rec,
p_paygroup_assign_rec IN PayGroup_assign_rec,
p_ovn IN NUMBER,
x_loading_status OUT NOCOPY VARCHAR2,
x_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_srp_assignment';
l_update_rec cn_srp_paygroup_pvt.paygroup_assign_rec;
SELECT pay_group_id
FROM cn_pay_groups_all
WHERE name = p_name
AND org_id = p_org_id;
SELECT srp_pay_group_id
FROM cn_srp_pay_groups_all
WHERE salesrep_id = c_salesrep_id
AND 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));
SAVEPOINT update_srp_assignment;
x_loading_status := 'CN_UPDATED';
FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
'cn.plsql.cn_srp_paygroup_pub.update_srp_assignment.error',
true);
FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
'cn.plsql.cn_srp_paygroup_pub.update_srp_assignment.org_validate',
'Validated org_id = ' || l_org_id || ' status = ' || l_status);
x_loading_status := 'CN_UPDATED';
x_loading_status := 'CN_UPDATED';
'UPDATE_SRP_ASSIGNMENT',
'B',
'C')
THEN
cn_srp_paygroup_pub_cuhk.update_srp_assignment_pre
(p_api_version => p_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_paygroup_assign_rec => l_payGroup_assign_rec,
p_old_paygroup_assign_rec => l_old_payGroup_assign_rec,
x_loading_status => x_loading_status,
x_status => x_status
);
'UPDATE_SRP_ASSIGNMENT',
'B',
'V')
THEN
cn_srp_paygroup_pub_vuhk.update_srp_assignment_pre
(p_api_version => p_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_paygroup_assign_rec => l_payGroup_assign_rec,
p_old_paygroup_assign_rec => l_old_payGroup_assign_rec,
x_loading_status => x_loading_status,
x_status => x_status
);
l_update_rec.srp_pay_group_id := l_srp_pay_group_id;
l_update_rec.salesrep_id := l_salesrep_id;
l_update_rec.pay_group_id := l_pay_group_id;
l_update_rec.assignment_start_date := l_paygroup_assign_rec.assignment_start_date;
l_update_rec.assignment_end_date := l_paygroup_assign_rec.assignment_end_date;
l_update_rec.lock_flag := l_paygroup_assign_rec.lock_flag;
l_update_rec.role_pay_group_id := l_paygroup_assign_rec.role_pay_group_id;
l_update_rec.org_id := l_org_id;
l_update_rec.attribute_category := l_paygroup_assign_rec.attribute_category;
l_update_rec.attribute1 := l_paygroup_assign_rec.attribute1;
l_update_rec.attribute2 := l_paygroup_assign_rec.attribute2;
l_update_rec.attribute3 := l_paygroup_assign_rec.attribute3;
l_update_rec.attribute4 := l_paygroup_assign_rec.attribute4;
l_update_rec.attribute5 := l_paygroup_assign_rec.attribute5;
l_update_rec.attribute6 := l_paygroup_assign_rec.attribute6;
l_update_rec.attribute7 := l_paygroup_assign_rec.attribute7;
l_update_rec.attribute8 := l_paygroup_assign_rec.attribute8;
l_update_rec.attribute9 := l_paygroup_assign_rec.attribute9;
l_update_rec.attribute10 := l_paygroup_assign_rec.attribute10;
l_update_rec.attribute11 := l_paygroup_assign_rec.attribute11;
l_update_rec.attribute12 := l_paygroup_assign_rec.attribute12;
l_update_rec.attribute13 := l_paygroup_assign_rec.attribute13;
l_update_rec.attribute14 := l_paygroup_assign_rec.attribute14;
l_update_rec.attribute15 := l_paygroup_assign_rec.attribute15;
cn_srp_paygroup_pvt.Update_Srp_Pay_Group
( p_api_version => 1.0,
x_return_status => x_return_status,
x_loading_status => x_loading_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_paygroup_assign_rec => l_update_rec);
'UPDATE_SRP_ASSIGNMENT',
'A',
'V')
THEN
cn_srp_paygroup_pub_vuhk.update_srp_assignment_post
(p_api_version => p_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_old_paygroup_assign_rec => l_old_payGroup_assign_rec,
p_paygroup_assign_rec => l_payGroup_assign_rec,
x_loading_status => x_loading_status,
x_status => x_status
);
'UPDATE_SRP_ASSIGNMENT',
'A',
'C')
THEN
cn_srp_paygroup_pub_cuhk.update_srp_assignment_post
(p_api_version => p_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_old_paygroup_assign_rec => l_old_payGroup_assign_rec,
p_paygroup_assign_rec => l_payGroup_assign_rec,
x_loading_status => x_loading_status,
x_status => x_status
);
'UPDATE_SRP_ASSIGNMENT',
'M',
'M')
THEN
IF cn_srp_paygroup_pub_cuhk.ok_to_generate_msg
(p_paygroup_assign_rec => l_paygroup_assign_rec)
THEN
-- Get a ID for workflow/ business object instance
l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
ROLLBACK TO update_srp_assignment;
ROLLBACK TO update_srp_assignment;
ROLLBACK TO update_srp_assignment;
END update_srp_assignment;
x_loading_status := 'CN_INSERTED';
select pay_group_id, start_date, end_date, org_id
into l_pay_group_id, l_pg_start_date, l_pg_end_date, l_org_id
from cn_role_pay_groups_all
where role_pay_group_id = p_role_pay_group_id;
select salesrep_id, start_date, end_date
into l_salesrep_id, l_srp_start_date, l_srp_end_date
from cn_srp_roles
where srp_role_id = p_srp_role_id
AND org_id = l_org_id;
select employee_number, type
into l_emp_num, l_salesrep_type
from cn_salesreps
where salesrep_id = l_salesrep_id
AND org_id = l_org_id;
select name
into l_pay_group_name
from cn_pay_groups_all
where pay_group_id = l_pay_group_id;
select count(*)
into l_count
from cn_srp_pay_groups_all
where salesrep_id = l_salesrep_id
AND org_id = l_org_id
and ((l_start_date between start_date and nvl(end_date,l_null_date))
or (nvl(l_end_date,l_null_date) between
start_date and nvl(end_date,l_null_date)));
PROCEDURE Update_Mass_Asgn_Srp_Pay
(
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_srp_role_id IN NUMBER,
p_role_pay_group_id IN NUMBER,
x_srp_pay_group_id OUT NOCOPY NUMBER,
x_loading_status OUT NOCOPY VARCHAR2
) IS
l_return_status VARCHAR2(2000);
l_api_name CONSTANT VARCHAR2(30) := 'Update_Mass_Asgn_Srp_Pay';
select srp_pay_group_id,pay_group_id, start_date, end_date,object_version_number,lock_flag
from cn_srp_pay_groups sp
where salesrep_id = l_salesrep_id_old
AND role_pay_group_id = p_role_pay_group_id
AND NOT EXISTS
( Select 1 from cn_srp_roles sr, cn_role_pay_groups rp
Where salesrep_id = l_salesrep_id_old
AND role_pay_group_id = p_role_pay_group_id
AND sr.role_id = rp.role_id
AND sr.org_id = rp.org_id
AND greatest(sr.start_date,rp.start_date) = sp.start_date
AND least(nvl(sr.end_date,l_null_date),nvl(rp.end_date,l_null_date))
= nvl(sp.end_date,l_null_date)
);
SAVEPOINT Update_Mass_Asgn_Srp_Pay;
x_loading_status := 'CN_UPDATED';
SELECT org_id
INTO l_org_id
FROM cn_role_pay_groups_all
WHERE role_pay_group_id = p_role_pay_group_id;
'cn.plsql.cn_srp_paygroup_pub.update_mass_asgn_srp_pay.org_validate',
'Validated org_id = ' || l_org_id || ' status = ' || l_status);
SELECT salesrep_id,role_id,start_date, end_date
INTO l_salesrep_id_old,l_role_id_old,l_srp_start_date_new, l_srp_end_date_new
FROM cn_srp_roles
WHERE srp_role_id = p_srp_role_id
AND org_id = l_org_id;
SELECT count(*)
INTO l_count
FROM cn_srp_pay_groups_all
WHERE salesrep_id = l_salesrep_id_old
AND role_pay_group_id = p_role_pay_group_id;
SELECT count(*) into l_count_pay
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
cn_payruns_all prun
WHERE w.salesrep_id = l_salesrep_id_old
AND w.org_id = l_org_id
AND prun.pay_period_id = prd.period_id
AND prun.payrun_id = w.payrun_id
AND prun.pay_group_id = l_pay_group_id_old
AND prd.org_id = l_org_id
AND ((prd.start_date BETWEEN l_start_date_old AND nvl(l_end_date_old,l_null_date)) OR
(prd.end_date between l_start_date_old AND nvl(l_end_date_old,l_null_date)) );
SELECT employee_number, type
INTO l_emp_num_old, l_salesrep_type_old
FROM cn_salesreps
WHERE salesrep_id = l_salesrep_id_old
AND org_id = l_org_id;
SELECT name
INTO l_pay_group_name_old
FROM cn_pay_groups_all
WHERE pay_group_id = l_pay_group_id_old;
SELECT salesrep_id, start_date, end_date
INTO l_salesrep_id_new, l_srp_start_date_new, l_srp_end_date_new
FROM cn_srp_roles
WHERE srp_role_id = p_srp_role_id
AND org_id = l_org_id;
SELECT employee_number, type
INTO l_emp_num_new, l_salesrep_type_new
FROM cn_salesreps
WHERE salesrep_id = l_salesrep_id_new
AND org_id = l_org_id;
SELECT pay_group_id, start_date, end_date
INTO l_pay_group_id_new, l_pp_start_date_new, l_pp_end_date_new
FROM cn_role_pay_groups_all
WHERE role_pay_group_id = p_role_pay_group_id;
SELECT name
INTO l_pay_group_name_new
FROM cn_pay_groups_all
WHERE pay_group_id = l_pay_group_id_new;
SELECT count(*)
INTO l_count_srp_pay_group
FROM cn_srp_pay_groups_all
WHERE salesrep_id=l_salesrep_id_old
AND org_id = l_org_id
AND ((l_start_date_old between start_date and nvl(end_date,l_null_date))
OR (l_end_date_old between start_date and nvl(end_date,l_null_date)));
Update_srp_Assignment
(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_old_paygroup_assign_rec => oldrec,
p_paygroup_assign_rec => newrec,
p_ovn => l_ovn_old ,
x_loading_status => l_loading_status,
x_status => l_status );
cn_srp_paygroup_pvt.delete_srp_pay_group
(
p_api_version => 1.0,
x_return_status => l_return_status,
x_loading_status => l_loading_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_paygroup_assign_rec => delrec
);
SELECT count(*)
INTO l_count_srp_pay_group
FROM cn_srp_pay_groups_all
WHERE salesrep_id=l_salesrep_id_old
AND org_id = l_org_id
AND ((l_start_date_new between start_date and nvl(end_date,l_null_date))
OR (l_end_date_new between start_date and nvl(end_date,l_null_date)));
cn_srp_paygroup_pvt.delete_srp_pay_group
(
p_api_version => 1.0,
x_return_status => l_return_status,
x_loading_status => l_loading_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_paygroup_assign_rec=> delrec
);
ROLLBACK TO Update_Mass_Asgn_Srp_Pay;
ROLLBACK TO Update_Mass_Asgn_Srp_Pay;
ROLLBACK TO Update_Mass_Asgn_Srp_Pay;
End Update_Mass_Asgn_Srp_Pay;