[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select org_id, pmt_plan_id, start_date, end_date
into l_org_id, l_pmt_plan_id, l_role_pp_start_date, l_role_pp_end_date
from cn_role_pmt_plans
where ROLE_PMT_PLAN_ID = p_role_pmt_plan_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_pp_start_date, l_pp_end_date
from cn_pmt_plans
where pmt_plan_id = l_pmt_plan_id;
SELECT COUNT(1) INTO l_dummy
FROM cn_srp_pmt_plans_all
WHERE salesrep_id = p_salesrep_id
AND pmt_plan_id = p_pmt_plan_id
AND start_date = p_start_date
AND ( (end_date = p_end_date) OR
(end_date IS NULL AND p_end_date IS NULL) )
AND ((p_srp_pmt_plan_id IS NOT NULL AND
srp_pmt_plan_id <> p_srp_pmt_plan_id)
OR
(p_srp_pmt_plan_id IS NULL));
SELECT start_date_active, end_date_active
INTO l_srp_start_date, l_srp_end_date
FROM cn_salesreps
WHERE salesrep_id = p_salesrep_id
AND org_id = p_org_id;
SELECT start_date, end_date, payment_group_code
INTO l_pp_start_date, l_pp_end_date, l_payment_group_code
FROM cn_pmt_plans_all
WHERE pmt_plan_id = p_pmt_plan_id;
SELECT COUNT(1) INTO l_dummy
FROM cn_srp_pmt_plans_all cspp, cn_pmt_plans_all cpp
WHERE (((cspp.end_date IS NULL)
AND (p_end_date IS NULL))
OR
((cspp.end_date IS NULL) AND
(p_end_date IS NOT NULL) AND
((p_start_date >= cspp.start_date) OR
(cspp.start_date BETWEEN p_start_date AND p_end_date))
)
OR
((cspp.end_date IS NOT NULL) AND
(p_end_date IS NULL) AND
((p_start_date <= cspp.start_date) OR
(p_start_date BETWEEN cspp.start_date AND cspp.end_date))
)
OR
((cspp.end_date IS NOT NULL) AND
(p_end_date IS NOT NULL) AND
((cspp.start_date BETWEEN p_start_date AND p_end_date) OR
(cspp.end_date BETWEEN p_start_date AND p_end_date) OR
(p_start_date BETWEEN cspp.start_date AND cspp.end_date))
)
)
AND ((p_srp_pmt_plan_id IS NOT NULL AND
srp_pmt_plan_id <> p_srp_pmt_plan_id)
OR
(p_srp_pmt_plan_id IS NULL))
AND cspp.Salesrep_id = p_salesrep_id
AND cpp.payment_group_code = l_payment_group_code
AND cspp.pmt_plan_id = cpp.pmt_plan_id;
SELECT DISTINCT prun.name
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
cn_payruns_all prun, cn_srp_pmt_plans_all spp
WHERE w.salesrep_id = spp.salesrep_id
AND w.quota_id is null
AND prun.pay_period_id = prd.period_id
AND prun.org_id = prd.org_id
AND prun.payrun_id = w.payrun_id
AND spp.srp_pmt_plan_id = p_srp_pmt_plan_id
AND ( ((spp.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
AND (prd.start_date <= spp.end_date)
AND (prd.end_date >= spp.start_date))
OR ((spp.end_date IS NULL) AND (prd.end_date IS NOT NULL)
AND (prd.end_date >= spp.start_date))
OR ((spp.end_date IS NULL) AND (prd.end_date IS NULL))
);
SELECT 'ERROR' as estatus
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
cn_payruns_all prun, cn_srp_pmt_plans_all spp,cn_pmt_plans_all pp
WHERE w.salesrep_id = spp.salesrep_id
AND w.quota_id is null
AND prun.pay_period_id = prd.period_id
AND prun.org_id = prd.org_id
AND prun.payrun_id = w.payrun_id
AND spp.srp_pmt_plan_id = p_srp_pmt_plan_id
AND spp.pmt_plan_id = pp.pmt_plan_id
AND prun.status = 'PAID'
AND ( ((spp.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
AND (prd.start_date <= spp.end_date)
AND (prd.end_date >= spp.start_date))
OR ((spp.end_date IS NULL) AND (prd.end_date IS NOT NULL)
AND (prd.end_date >= spp.start_date))
OR ((spp.end_date IS NULL) AND (prd.end_date IS NULL))
);
SELECT 'ERROR' as estatus
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
cn_payruns_all prun, cn_srp_pmt_plans_all spp,cn_pmt_plans_all pp
-- cn_payment_transactions pt
WHERE w.salesrep_id = spp.salesrep_id
AND w.quota_id is null
AND prun.pay_period_id = prd.period_id
AND prun.org_id = prd.org_id
AND prun.payrun_id = w.payrun_id
AND spp.srp_pmt_plan_id = p_srp_pmt_plan_id
AND spp.pmt_plan_id = pp.pmt_plan_id
-- AND pt.payrun_id = prun.payrun_id
-- AND pt.payee_salesrep_id = w.salesrep_id
-- AND pt.pay_period_id = prun.pay_period_id
-- AND pt.incentive_type_code = 'PMTPLN'
AND ( ((spp.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
AND (prd.start_date <= spp.end_date)
AND (prd.end_date >= spp.start_date))
OR ((spp.end_date IS NULL) AND (prd.end_date IS NOT NULL)
AND (prd.end_date >= spp.start_date))
OR ((spp.end_date IS NULL) AND (prd.end_date IS NULL))
);
SELECT DISTINCT prun.name
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,cn_payruns_all prun
WHERE w.salesrep_id = p_salesrep_id
AND prun.pay_period_id = prd.period_id
AND w.quota_id is null
AND prun.org_id = prd.org_id
AND prun.payrun_id = w.payrun_id
AND l_old_start_date < p_start_date
AND prd.start_date < p_start_date
AND prd.end_date > l_old_start_date
UNION
SELECT DISTINCT prun.name
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,cn_payruns_all prun
WHERE w.salesrep_id = p_salesrep_id
AND prun.pay_period_id = prd.period_id
AND prun.org_id = prd.org_id
AND w.quota_id is null
AND prun.payrun_id = w.payrun_id
AND l_fixed_old_end_date > l_fixed_end_date
AND prd.start_date < l_fixed_old_end_date
AND prd.end_date > l_fixed_end_date;
SELECT 'ERROR' as estatus
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,cn_payruns_all prun
WHERE w.salesrep_id = p_salesrep_id
AND prun.pay_period_id = prd.period_id
AND w.quota_id is null
AND prun.org_id = prd.org_id
AND prun.status = 'PAID'
AND prun.payrun_id = w.payrun_id
AND l_old_start_date < p_start_date
AND prd.start_date < p_start_date
AND prd.end_date > l_old_start_date
UNION
SELECT 'ERROR' as estatus
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,cn_payruns_all prun
WHERE w.salesrep_id = p_salesrep_id
AND prun.pay_period_id = prd.period_id
AND prun.org_id = prd.org_id
AND prun.status = 'PAID'
AND w.quota_id is null
AND prun.payrun_id = w.payrun_id
AND l_fixed_old_end_date > l_fixed_end_date
AND prd.start_date < l_fixed_old_end_date
AND prd.end_date > l_fixed_end_date;
SELECT start_date, Nvl(end_date, l_end_of_time) as end_date
FROM cn_srp_pmt_plans_all
WHERE srp_pmt_plan_id = param_srp_pmt_plan_id;
SELECT 'ERROR' as estatus
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
cn_payruns_all prun, cn_srp_pmt_plans_all spp
-- cn_payment_transactions pt
WHERE w.salesrep_id = spp.salesrep_id
AND w.quota_id is null
AND prun.pay_period_id = prd.period_id
AND prun.org_id = prd.org_id
AND prun.payrun_id = w.payrun_id
AND spp.srp_pmt_plan_id = p_srp_pmt_plan_id
AND prun.status<>'PAID'
-- AND pt.payrun_id = prun.payrun_id
-- AND pt.payee_salesrep_id = w.salesrep_id
-- AND pt.pay_period_id = prun.pay_period_id
-- AND pt.incentive_type_code = 'PMTPLN'
AND (((spp.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
AND (prd.start_date <= spp.end_date)
AND (prd.end_date >= spp.start_date))
OR ((spp.end_date IS NULL) AND (prd.end_date IS NOT NULL)
AND (prd.end_date >= spp.start_date))
OR ((spp.end_date IS NULL) AND (prd.end_date IS NULL)))
AND (NVL(p_end_date, l_end_of_time) < NVL(prd.end_date, l_end_of_time)
OR p_start_date > prd.start_date);
* As per the latest update received, the behaviour should be as follows,
* If a resource has been paid or has a working/unpaid worksheet then we should
* not allow users to delete the payment plan for that period even if no
* payment plan adjustments are there in the worksheet.
* Same applies to shrinking. Can happen only till the period end date of the
* latest paid/unpaid worksheet.
-----------------------------------------------------------------------------*/
-- Initialize message list
FND_MSG_PUB.initialize;
IF p_operation = 'DELETE' THEN
-- check payruns involved
OPEN get_paid_del_payruns;
ELSIF p_operation = 'UPDATE' THEN
open get_date_range(p_srp_pmt_plan_id);
SELECT COUNT(1) INTO l_dummy
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
cn_payruns_all prun, cn_payment_transactions_all pmttrans
WHERE w.salesrep_id = p_salesrep_id
AND w.salesrep_id = pmttrans.credited_salesrep_id
AND pmttrans.incentive_type_code = 'PMTPLN'
AND prun.pay_period_id = prd.period_id
AND prun.org_id = prd.org_id
AND prun.payrun_id = w.payrun_id
AND ( ((p_end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
AND (prd.start_date <= p_end_date)
AND (prd.end_date >= p_start_date))
OR ((p_end_date IS NULL) AND (prd.end_date IS NOT NULL)
AND (prd.end_date >= p_start_date))
OR ((p_end_date IS NULL) AND (prd.end_date IS NULL))
);
SELECT COUNT(1) INTO l_dummy
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,cn_payruns_all prun
WHERE w.salesrep_id = p_salesrep_id
AND prun.pay_period_id = prd.period_id
AND prun.org_id = prd.org_id
AND prun.payrun_id = w.payrun_id
AND ( ((p_old_end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
AND (prd.start_date < p_old_end_date)
AND (prd.end_date > p_end_date))
OR ((p_old_end_date IS NULL) AND
((prd.start_date > p_end_date) OR (prd.end_date > p_end_date)))
);
SELECT COUNT(1) INTO l_dummy
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,cn_payruns_all prun
WHERE w.salesrep_id = p_salesrep_id
AND prun.pay_period_id = prd.period_id
AND prun.org_id = prd.org_id
AND prun.payrun_id = w.payrun_id
AND ( ((p_old_end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
AND (prd.start_date <= p_old_end_date)
AND (prd.end_date >= p_old_start_date))
OR ((p_old_end_date IS NULL) AND (prd.end_date IS NOT NULL)
AND (prd.end_date >= p_old_start_date))
);
FND_MESSAGE.SET_NAME ('CN' , 'CN_SPP_UPDATE_NOT_ALLOWED');
x_loading_status := 'CN_SPP_UPDATE_NOT_ALLOWED';
END IF; -- end if delete/update operation
ELSIF (p_operation = 'Update') THEN
l_key := l_key || '-' || p_pmt_plan_assign_rec.object_version_number;
l_list.DELETE;
select r.name
from cn_roles r, cn_role_pmt_plans_all rpp
where r.role_id = rpp.role_id
and rpp.role_pmt_plan_id = p_pmt_plan_assign_rec.role_pmt_plan_id;
select credit_type_id, name into l_credit_type_id, l_name
from cn_pmt_plans_all
where pmt_plan_id = p_pmt_plan_assign_rec.pmt_plan_id;
cn_srp_pmt_plans_pkg.insert_row
( x_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id
,x_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id
,x_salesrep_id => p_pmt_plan_assign_rec.salesrep_id
,x_org_id => p_pmt_plan_assign_rec.org_id
,x_role_id => NULL
,x_credit_type_id => l_credit_type_id -- obsolete
,x_start_date => p_pmt_plan_assign_rec.start_date
,x_end_date => p_pmt_plan_assign_rec.end_date
,x_minimum_amount => p_pmt_plan_assign_rec.minimum_amount
,x_maximum_amount => p_pmt_plan_assign_rec.maximum_amount
,x_max_recovery_amount => NULL -- obsolete
,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_srp_role_id => p_pmt_plan_assign_rec.srp_role_id
,x_role_pmt_plan_id => p_pmt_plan_assign_rec.role_pmt_plan_id
,x_lock_flag => p_pmt_plan_assign_rec.lock_flag
);
SELECT object_version_number
INTO p_pmt_plan_assign_rec.object_version_number
FROM cn_srp_pmt_plans_all
WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
PROCEDURE Update_Srp_Pmt_Plan
( 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_loading_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_pmt_plan_assign_rec IN OUT NOCOPY pmt_plan_assign_rec ) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Srp_Pmt_Plan';
SELECT *
FROM cn_srp_pmt_plans
WHERE srp_pmt_plan_id = l_srp_pmt_plan_id;
SAVEPOINT Update_Srp_Pmt_Plan;
x_loading_status := 'CN_UPDATED';
fnd_message.set_name('CN', 'CN_CANNOT_UPDATE_LOCK');
x_loading_status := 'CN_CANNOT_UPDATE_LOCK';
fnd_message.set_name('CN', 'CN_CANNOT_UPDATE_LOCK');
x_loading_status := 'CN_CANNOT_UPDATE_LOCK';
select credit_type_id into l_credit_type_id
from cn_pmt_plans_all
where pmt_plan_id = p_pmt_plan_assign_rec.pmt_plan_id;
cn_srp_pmt_plans_pkg.delete_row
(x_srp_pmt_plan_id => l_oldrec.srp_pmt_plan_id);
cn_srp_pmt_plans_pkg.insert_row
( x_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id
,x_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id
,x_salesrep_id => p_pmt_plan_assign_rec.salesrep_id
,x_org_id => p_pmt_plan_assign_rec.org_id
,x_role_id => NULL
,x_credit_type_id => l_credit_type_id -- obsolete
,x_start_date => p_pmt_plan_assign_rec.start_date
,x_end_date => p_pmt_plan_assign_rec.end_date
,x_minimum_amount => p_pmt_plan_assign_rec.minimum_amount
,x_maximum_amount => p_pmt_plan_assign_rec.maximum_amount
,x_max_recovery_amount => NULL -- obsolete
,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_srp_role_id => p_pmt_plan_assign_rec.srp_role_id
,x_role_pmt_plan_id => p_pmt_plan_assign_rec.role_pmt_plan_id
,x_lock_flag => p_pmt_plan_assign_rec.lock_flag);
update cn_srp_pmt_plans_all
set srp_pmt_plan_id = l_oldrec.srp_pmt_plan_id
where srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
-- Check if update operation allowed
-- try to update start date, end date, need to check if the old_rec
-- already been used in worksheet during those delete dates,if so,
-- cannot change the date range
-- Added more parameters
check_operation_allowed
( x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_salesrep_id => l_oldrec.salesrep_id,
p_old_start_date => l_oldrec.start_date,
p_old_end_date => l_oldrec.end_date,
p_start_date => p_pmt_plan_assign_rec.start_date,
p_end_date => p_pmt_plan_assign_rec.end_date,
p_loading_status => x_loading_status,
x_loading_status => x_loading_status
);
cn_srp_pmt_plans_pkg.update_row
( x_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id
,x_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id
,x_salesrep_id => p_pmt_plan_assign_rec.salesrep_id
,x_org_id => p_pmt_plan_assign_rec.org_id
,x_role_id => NULL
,x_credit_type_id => l_credit_type_id -- Obsolete
,x_start_date => p_pmt_plan_assign_rec.start_date
,x_end_date => p_pmt_plan_assign_rec.end_date
,x_minimum_amount => p_pmt_plan_assign_rec.minimum_amount
,x_maximum_amount => p_pmt_plan_assign_rec.maximum_amount
,x_max_recovery_amount => NULL -- Obsolete
,x_last_update_date => sysdate
,x_last_updated_by => fnd_global.user_id
,x_last_update_login => fnd_global.login_id
,x_object_version_number => p_pmt_plan_assign_rec.object_version_number
,x_lock_flag => p_pmt_plan_assign_rec.lock_flag
);
UPDATE cn_srp_pmt_plans_all
SET role_pmt_plan_id = NULL,
srp_role_id = NULL
WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
(p_operation => 'Update',
p_pmt_plan_assign_rec => p_pmt_plan_assign_rec);
SELECT name INTO l_oldname FROM cn_pmt_plans_all
WHERE pmt_plan_id = l_oldrec.pmt_plan_id;
SELECT name INTO l_newname FROM cn_pmt_plans_all
WHERE pmt_plan_id = p_pmt_plan_assign_rec.pmt_plan_id;
SELECT object_version_number
INTO p_pmt_plan_assign_rec.object_version_number
FROM cn_srp_pmt_plans_all
WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
ROLLBACK TO Update_Srp_Pmt_Plan;
ROLLBACK TO Update_Srp_Pmt_Plan;
ROLLBACK TO Update_Srp_Pmt_Plan;
END update_srp_pmt_plan;
PROCEDURE valid_delete_srp_pmt_plan
( p_srp_pmt_plan_id IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Valid_Delete_Srp_Pmt_Plan';
SELECT *
FROM cn_srp_pmt_plans
WHERE srp_pmt_plan_id = l_srp_pmt_plan_id;
x_loading_status := 'CN_DELETED';
END valid_delete_srp_pmt_plan;
PROCEDURE Delete_Srp_Pmt_Plan
( 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_loading_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_srp_pmt_plan_id IN NUMBER) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Srp_Pmt_Plan';
select p.name, r.name role_name, spp.start_date, spp.end_date, spp.minimum_amount, spp.maximum_amount, spp.lock_flag, spp.salesrep_id
from cn_srp_pmt_plans_all spp, cn_pmt_plans_all p, cn_role_pmt_plans_all rpp, cn_roles r
where spp.srp_pmt_plan_id = p_srp_pmt_plan_id
and spp.role_pmt_plan_id = rpp.role_pmt_plan_id(+)
and spp.pmt_plan_id = p.pmt_plan_id
and rpp.role_id = r.role_id(+);
SAVEPOINT delete_srp_pmt_plan;
x_loading_status := 'CN_DELETED';
valid_delete_srp_pmt_plan
( p_srp_pmt_plan_id => p_srp_pmt_plan_id,
p_init_msg_list => p_init_msg_list,
x_loading_status => x_loading_status,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
cn_srp_pmt_plans_pkg.delete_row
(x_srp_pmt_plan_id => p_srp_pmt_plan_id);
ROLLBACK TO Delete_Srp_Pmt_Plan;
ROLLBACK TO Delete_Srp_Pmt_Plan;
ROLLBACK TO Delete_Srp_Pmt_Plan;
END Delete_Srp_Pmt_Plan;
select pmt_plan_id, start_date, end_date
into l_pmt_plan_id, l_pp_start_date, l_pp_end_date
from cn_role_pmt_plans
where role_pmt_plan_id = p_role_pmt_plan_id;
select minimum_amount, maximum_amount, org_id
into l_min_amt, l_max_amt, l_org_id
from cn_pmt_plans
where pmt_plan_id = l_pmt_plan_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;
PROCEDURE Update_Mass_Asgn_Srp_Pmt_plan
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
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_pmt_plan_id IN NUMBER,
x_loading_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Mass_Asgn_Srp_Pmt_Plan';
SAVEPOINT Update_Mass_Asgn_Srp_Pmt_plan;
x_loading_status := 'CN_PP_UPDATED';
select org_id into l_org_id
from cn_role_pmt_plans
where role_pmt_plan_id = p_role_pmt_plan_id;
select salesrep_id
into l_salesrep_id_old
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_pmt_plans
where salesrep_id = l_salesrep_id_old
AND srp_role_id = p_srp_role_id
AND role_pmt_plan_id = p_role_pmt_plan_id;
select spp.start_date, spp.end_date, spp.salesrep_id,
crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
into l_start_date_old, l_end_date_old, l_salesrep_id_old,
l_role_pp_start_date, l_role_pp_end_date, l_srp_pmt_plan_id
from cn_srp_pmt_plans_all spp, cn_pmt_plans_all cpp, cn_role_pmt_plans_all crpp
where spp.srp_role_id = p_srp_role_id
AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
AND crpp.role_pmt_plan_id = p_role_pmt_plan_id
AND cpp.pmt_plan_id = spp.pmt_plan_id;
select pmt_plan_id, start_date, end_date
into l_pmt_plan_id_new, l_pp_start_date_new, l_pp_end_date_new
from cn_role_pmt_plans
where role_pmt_plan_id = p_role_pmt_plan_id;
select minimum_amount, maximum_amount, payment_group_code, org_id
into l_min_amt_new, l_max_amt_new, l_pgc, l_org_id
from cn_pmt_plans
where pmt_plan_id = l_pmt_plan_id_new;
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 count(*) into l_count_srp_pmt_plan from cn_srp_pmt_plans where salesrep_id=l_salesrep_id_old
and ((l_pp_start_date_new between start_date and nvl(end_date,l_null_date))
or (nvl(l_pp_end_date_new,l_null_date) between start_date and nvl(end_date,l_null_date)));
SELECT COUNT(*) INTO l_count_srp_pmt_plan
FROM cn_srp_pmt_plans cspp, cn_pmt_plans cpp
WHERE cspp.salesrep_id = l_salesrep_id_old
AND ((l_pp_start_date_new BETWEEN cspp.start_date AND NVL(cspp.end_date,l_pp_start_date_new))
OR (NVL(l_pp_end_date_new,l_pp_start_date_new) BETWEEN cspp.start_date AND NVL(cspp.end_date,l_pp_end_date_new)))
AND cspp.pmt_plan_id = cpp.pmt_plan_id
AND cpp.payment_group_code = l_pgc;
SELECT count(*) into l_worksheets
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
cn_payruns_all prun, cn_srp_pmt_plans_all spp
WHERE w.salesrep_id = spp.salesrep_id
AND w.quota_id is null
AND prun.pay_period_id = prd.period_id
AND prun.org_id = prd.org_id
AND prun.payrun_id = w.payrun_id
AND spp.srp_pmt_plan_id = l_srp_pmt_plan_id
AND (((spp.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
AND (prd.start_date <= spp.end_date)
AND (prd.end_date >= spp.start_date))
OR ((spp.end_date IS NULL) AND (prd.end_date IS NOT NULL)
AND (prd.end_date >= spp.start_date))
OR ((spp.end_date IS NULL) AND (prd.end_date IS NULL)))
AND (NVL(l_end_date_new, l_end_of_time) < NVL(prd.end_date, l_end_of_time)
OR l_start_date_new > prd.start_date);
SELECT count(*) into l_worksheets
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
cn_payruns_all prun, cn_srp_pmt_plans_all spp
WHERE w.salesrep_id = spp.salesrep_id
AND w.quota_id is null
AND prun.pay_period_id = prd.period_id
AND prun.org_id = prd.org_id
AND prun.payrun_id = w.payrun_id
AND spp.srp_pmt_plan_id = l_srp_pmt_plan_id
AND prun.status ='UNPAID'
AND (((spp.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
AND (prd.start_date <= spp.end_date)
AND (prd.end_date >= spp.start_date))
OR ((spp.end_date IS NULL) AND (prd.end_date IS NOT NULL)
AND (prd.end_date >= spp.start_date))
OR ((spp.end_date IS NULL) AND (prd.end_date IS NULL)))
AND (NVL(l_end_date_new, l_end_of_time) >= NVL(prd.end_date, l_end_of_time)
OR l_start_date_new <= prd.start_date);
update_srp_pmt_plan
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_pmt_plan_assign_rec=> newrec,
x_loading_status => l_loading_status);
FND_MESSAGE.Set_Name('CN', 'CN_SPP_UPDATE_NOT_ALLOWED');
SELECT count(*)
INTO l_count_srp_pmt_plan
FROM cn_srp_pmt_plans
WHERE salesrep_id = l_salesrep_id_new
AND org_id = l_org_id
AND ((l_start_date_new between start_date and nvl(end_date,l_end_of_time))
OR (nvl(l_end_date_new,l_end_of_time) between
start_date and nvl(end_date,l_end_of_time)));
-- only delete if exists
IF l_srp_pmt_plan_id IS NOT NULL THEN
delete_srp_pmt_plan
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_srp_pmt_plan_id => l_srp_pmt_plan_id,
x_loading_status => l_loading_status);
ROLLBACK TO Update_Mass_Asgn_Srp_Pmt_plan;
ROLLBACK TO Update_Mass_Asgn_Srp_Pmt_plan;
ROLLBACK TO Update_Mass_Asgn_Srp_Pmt_plan;
End Update_Mass_Asgn_Srp_Pmt_plan;
PROCEDURE Delete_Mass_Asgn_Srp_Pmt_Plan
(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
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_pmt_plan_id IN NUMBER,
x_loading_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Mass_Asgn_Srp_Pmt_Plan';
SELECT *
FROM cn_srp_pmt_plans
WHERE srp_pmt_plan_id = l_srp_pmt_plan_id;
SAVEPOINT Delete_Mass_Asgn_Srp_Pmt_Plan;
x_loading_status := 'CN_PP_DELETED';
select spp.start_date, spp.end_date, spp.salesrep_id,
cpp.minimum_amount, cpp.maximum_amount,
crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
into l_start_date, l_end_date, l_salesrep_id,
l_min_amt, l_max_amt,
l_role_pp_start_date, l_role_pp_end_date, l_srp_pmt_plan_id
from cn_srp_pmt_plans spp, cn_pmt_plans cpp, cn_role_pmt_plans crpp
where spp.srp_role_id = p_srp_role_id
AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
AND crpp.role_pmt_plan_id = p_role_pmt_plan_id
AND cpp.pmt_plan_id = spp.pmt_plan_id;
SELECT COUNT(1) INTO l_dummy
FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
cn_payruns_all prun, cn_payment_transactions_all pmttrans
WHERE w.salesrep_id = l_spp_rec.salesrep_id
AND w.salesrep_id = pmttrans.credited_salesrep_id
AND pmttrans.incentive_type_code = 'PMTPLN'
AND prun.pay_period_id = prd.period_id
AND prun.org_id = prd.org_id
AND prun.payrun_id = w.payrun_id
AND ( ((l_spp_rec.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
AND (prd.start_date <= l_spp_rec.end_date)
AND (prd.end_date >= l_spp_rec.start_date))
OR ((l_spp_rec.end_date IS NULL) AND (prd.end_date IS NOT NULL)
AND (prd.end_date >= l_spp_rec.start_date))
OR ((l_spp_rec.end_date IS NULL) AND (prd.end_date IS NULL))
);
update cn_srp_pmt_plans set srp_role_id = null, role_pmt_plan_id = null
where srp_pmt_plan_id = l_srp_pmt_plan_id;
delete_srp_pmt_plan
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_srp_pmt_plan_id => l_srp_pmt_plan_id,
x_loading_status => l_loading_status);
ROLLBACK TO Delete_Mass_Asgn_Srp_Pmt_Plan;
ROLLBACK TO Delete_Mass_Asgn_Srp_Pmt_Plan;
ROLLBACK TO Delete_Mass_Asgn_Srp_Pmt_Plan;
END Delete_Mass_Asgn_Srp_Pmt_Plan;