The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cp.period_id,cp.period_name,ccpit.interval_number,ccpit.cal_per_int_type_id
FROM cn_period_statuses cp,cn_cal_per_int_types ccpit
WHERE period_year = p_period_year and cp.period_id = ccpit.cal_period_id and interval_type_id = -1002;
SELECT COUNT(*) INTO l_count FROM CN_SEAS_SCHEDULES WHERE UPPER(NAME) LIKE UPPER(p_seas_schedules_rec_type.name);
select min(start_date),max(end_date) into l_start_date,l_end_date
from cn_period_statuses
where period_year = p_seas_schedules_rec_type.period_year
group by period_year;
CN_SEAS_SCHEDULES_pkg.insert_row (
P_SEAS_SCHEDULE_ID => p_seas_schedules_rec_type.seas_schedule_id,
P_NAME => p_seas_schedules_rec_type.name,
P_DESCRIPTION => p_seas_schedules_rec_type.description,
P_PERIOD_YEAR => p_seas_schedules_rec_type.period_year,
P_START_DATE => l_start_date,
P_END_DATE => l_end_date,
P_VALIDATION_STATUS => 'INVALID'
);
SELECT SEAS_SCHEDULE_ID INTO l_seas_schedule_id
FROM CN_SEAS_SCHEDULES WHERE NAME like p_seas_schedules_rec_type.name;
SELECT COUNT(*) INTO l_cal_int_types
FROM cn_period_statuses cp,cn_cal_per_int_types ccpit
WHERE period_year = p_seas_schedules_rec_type.period_year and cp.period_id = ccpit.cal_period_id and interval_type_id = -1002;
CN_SEASONALITIES_pkg.insert_row(
P_SEASONALITY_ID => -99,
P_SEAS_SCHEDULE_ID => l_seas_schedule_id,
P_CAL_PER_INT_TYPE_ID => l_seas_sch.CAL_PER_INT_TYPE_ID,
P_PERIOD_ID => l_seas_sch.PERIOD_ID,
P_PCT_SEASONALITY => 0.0
);
PROCEDURE Update_Seas_Schedule
( 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,
p_seas_schedules_rec_type IN seas_schedules_rec_type,
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) := 'Update_Seas_Schedule';
SAVEPOINT Update_Seas_Schedule;
select COUNT(*) INTO l_srp_count from cn_srp_role_dtls t1,cn_role_quota_cates t2,cn_srp_roles t3
where t1.srp_role_id = t3.srp_role_id
and t2.role_id = t3.role_id
and t1.status not in ('PENDING')
and t2.seas_schedule_id = p_seas_schedules_rec_type.seas_schedule_id;
SELECT COUNT(*) INTO l_count FROM CN_SEAS_SCHEDULES WHERE UPPER(NAME) LIKE UPPER(p_seas_schedules_rec_type.name) AND SEAS_SCHEDULE_ID <> p_seas_schedules_rec_type.seas_schedule_id;
CN_SEAS_SCHEDULES_pkg.update_row
(
P_SEAS_SCHEDULE_ID => p_seas_schedules_rec_type.seas_schedule_id,
P_NAME => p_seas_schedules_rec_type.name,
P_DESCRIPTION => p_seas_schedules_rec_type.description,
P_PERIOD_YEAR => p_seas_schedules_rec_type.period_year,
P_START_DATE => l_start_date,
P_END_DATE => l_end_date,
P_VALIDATION_STATUS => l_validation_status,
p_object_version_number => p_seas_schedules_rec_type.object_version_number
);
<< end_Update_Seas_Schedule >>
NULL;
ROLLBACK TO Update_Seas_Schedule ;
ROLLBACK TO Update_Seas_Schedule ;
ROLLBACK TO Update_Seas_Schedule ;
END Update_Seas_Schedule;
PROCEDURE Delete_Seas_Schedule
( 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,
P_SEAS_SCHEDULE_ID IN cn_seas_schedules.seas_schedule_id%TYPE,
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) := 'Delete_Seas_Schedule';
SAVEPOINT Delete_Seas_Schedule;
SELECT COUNT(role_id) INTO l_role_quota_cate_count FROM CN_ROLE_QUOTA_CATES WHERE seas_schedule_id = P_SEAS_SCHEDULE_ID;
CN_SEAS_SCHEDULES_pkg.delete_row (P_SEAS_SCHEDULE_ID => l_seas_sch_id);
CN_SEASONALITIES_pkg.delete_row (P_SEAS_SCHEDULE_ID => l_seas_sch_id);
<< end_Delete_Seas_Schedule >>
NULL;
ROLLBACK TO Delete_Seas_Schedule ;
ROLLBACK TO Delete_Seas_Schedule ;
ROLLBACK TO Delete_Seas_Schedule ;
END Delete_Seas_Schedule;
select seas_schedule_id,sum(pct_seasonality) into l_seas_schedule_id,l_pct_seasonality
from cn_seasonalities
where seas_schedule_id = p_seas_schedules_id group by seas_schedule_id;
<< end_Delete_Seas_Schedule >>
NULL;