[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_tpc_enroll_status_chg (p_event_id IN ota_events.event_id%TYPE,
p_person_id IN ota_training_plans.person_id%TYPE,
-- Modified for Bug#3479186
p_contact_id IN ota_training_plans.contact_id%TYPE,
p_learning_path_ids OUT NOCOPY varchar2)
--p_status_id in ota_booking_status_types.booking_status_type_id%type)
IS
l_proc VARCHAR2(72) := g_package|| 'update_tpc_enroll_status_chg';
SELECT otpm.training_plan_member_id,
otpm.object_version_number,otpm.earliest_start_date, otpm.target_completion_date
-- otpm.member_status_type_id
FROM ota_training_plan_members otpm,
ota_training_plans otp
WHERE otp.training_plan_id = otpm.training_plan_id
-- AND otp.person_id = p_person_id
AND (( p_person_id IS NOT NULL AND otp.person_id = p_person_id)
OR (p_contact_id IS NOT NULL AND otp.contact_id = p_contact_id))
AND otpm.activity_version_id = csr_activity_version_id
and otpm.member_status_type_id <> 'CANCELLED'
-- AND otpm.target_completion_date IS NOT NULL
--Modified for Bug#3855721
AND(otp.learning_path_id IS NOT NULL OR ( otpm.earliest_start_date <= csr_evt_start_date
AND
(csr_evt_end_date IS NOT NULL
AND otpm.target_completion_date >= csr_evt_end_date)
or (csr_evt_type = 'SELFPACED'
AND otpm.target_completion_date >= csr_evt_start_date)));
SELECT activity_version_id,
course_start_date,
course_end_date,
event_type
FROM ota_events
WHERE event_id = p_event_id;
ota_tpm_api.update_training_plan_member
(p_effective_date => sysdate
,p_object_version_number => rec.object_version_number
,p_training_plan_member_id => rec.training_plan_member_id
,p_member_status_type_id => l_member_status_type
,p_earliest_start_date => rec.earliest_start_date
,p_target_completion_date => rec.target_completion_date
,p_activity_version_id => l_activity_version_id);
Update_tp_tpc_change(rec.training_plan_member_id, p_learning_path_ids);
OPEN csr_tp_update(rec1.training_plan_id);
FETCH csr_tp_update into l_name,l_object_version_number,l_time_period_id,l_budget_currency;
IF csr_tp_update%FOUND then
CLOSE csr_tp_update;
ota_tps_api.update_training_plan
(p_effective_date => sysdate
,p_training_plan_id => rec1.training_plan_id
,p_object_version_NUMBER => l_object_version_number
,p_plan_status_type_id => 'COMPLETED'
,p_name => l_name
,p_time_period_id => l_time_period_id
,p_budget_currency => l_budget_currency);
CLOSE csr_tp_update;
END update_tpc_enroll_status_chg;
PROCEDURE update_tpc_evt_change (p_event_id IN ota_Events.event_id%TYPE,
p_course_start_date IN ota_events.course_start_date%TYPE,
p_course_end_date IN ota_events.course_end_date%TYPE)
IS
/* Commented out for bug#5086156
CURSOR csr_tpm IS
SELECT tpm.training_plan_member_id,
tp.person_id,
-- Modified for Bug#3479186
tp.contact_id,
tpm.object_version_number
FROM ota_training_plans tp,
ota_training_plan_members tpm,
ota_events oe,
ota_delegate_bookings odb,
ota_booking_status_types bst
WHERE oe.event_id = odb.event_id
AND odb.booking_status_type_id=bst.booking_status_type_id
AND bst.type <>'C'
-- and bst.active_flag='Y'
AND oe.activity_version_id = tpm.activity_version_id
AND tpm.training_plan_id = tp.training_plan_id
--Modified for Bug#3855721
-- AND odb.delegate_person_id = tp.person_id
AND (odb.delegate_person_id = tp.person_id OR odb.delegate_contact_id = tp.contact_id)
AND oe.event_id = p_event_id
AND tpm.member_status_type_id NOT IN ('CANCELLED', 'OTA_AWAITING_APPROVAL','OTA_COMPLETED')
AND (tpm.target_completion_date nvl(p_course_end_date, hr_api.g_eot)) ;
l_proc VARCHAR2(72) := g_package|| 'update_tpc_evt_date_change';
END update_tpc_evt_change;
if p_mode= 'INSERT' then
ota_tpm_swi.create_training_plan_member
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
,p_training_plan_id => p_training_plan_id
,p_activity_version_id => p_activity_version_id
,p_activity_definition_id => p_activity_definition_id
,p_member_status_type_id => p_member_status_type_id
,p_target_completion_date => p_target_completion_date
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_attribute21 => p_attribute21
,p_attribute22 => p_attribute22
,p_attribute23 => p_attribute23
,p_attribute24 => p_attribute24
,p_attribute25 => p_attribute25
,p_attribute26 => p_attribute26
,p_attribute27 => p_attribute27
,p_attribute28 => p_attribute28
,p_attribute29 => p_attribute29
,p_attribute30 => p_attribute30
,p_assignment_id => p_assignment_id
,p_source_id => p_source_id
,p_source_function => p_source_function
,p_cancellation_reason => p_cancellation_reason
,p_earliest_start_date => p_earliest_start_date
,p_training_plan_member_id => l_training_plan_member_id
,p_object_version_NUMBER => l_object_version_number
,p_creator_person_id => p_creator_person_id
,p_return_status => p_return_status
);
elsif p_mode = 'UPDATE' then
ota_tpm_swi.update_training_plan_member
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_training_plan_member_id => p_training_plan_member_id
,p_object_version_NUMBER => p_object_version_number
,p_activity_version_id => p_activity_version_id
,p_activity_definition_id => p_activity_definition_id
,p_member_status_type_id => p_member_status_type_id
,p_target_completion_date => p_target_completion_date
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_attribute21 => p_attribute21
,p_attribute22 => p_attribute22
,p_attribute23 => p_attribute23
,p_attribute24 => p_attribute24
,p_attribute25 => p_attribute25
,p_attribute26 => p_attribute26
,p_attribute27 => p_attribute27
,p_attribute28 => p_attribute28
,p_attribute29 => p_attribute29
,p_attribute30 => p_attribute30
,p_assignment_id => p_assignment_id
,p_source_id => p_source_id
,p_source_function => p_source_function
,p_cancellation_reason => p_cancellation_reason
,p_earliest_start_date => p_earliest_start_date
,p_creator_person_id => p_creator_person_id
,p_return_status => p_return_status
);
Procedure Update_tpc_sshr_change(
p_training_plan_member_id ota_training_plan_members.training_plan_member_id%type,
p_person_id ota_training_plans.person_id%type,
p_mode varchar2)
is
Cursor get_tpc_detail
is
Select object_version_number from
ota_training_plan_members
where training_plan_member_id=p_training_plan_member_id;
ota_tpm_api.update_training_plan_member
(p_effective_date => sysdate
,p_object_version_number => l_object_version_number
,p_training_plan_member_id => p_training_plan_member_id
,p_member_status_type_id => l_member_status_type);
ota_tpm_api.update_training_plan_member
(p_effective_date => sysdate
,p_object_version_number => l_object_version_number
,p_training_plan_member_id => p_training_plan_member_id
,p_member_status_type_id => l_member_status_type);
end Update_tpc_sshr_change;
Procedure Update_tp_tpc_change
(p_training_plan_member_id ota_training_plan_members.training_plan_member_id%type)
is
--get all the TP corresponding to a particular TP member
CURSOR csr_tp_with_tpc
IS
SELECT otp.training_plan_id
, otp.plan_status_type_id
FROM ota_training_plans otp,
ota_training_plan_members otpm
WHERE otp.training_plan_id=otpm.training_plan_id
and otp.plan_status_type_id <> 'CANCELLED'
--and otp.plan_status_type_id <> 'COMPLETED'
-- Modified from COMPLETED to OTA_COMPLETED
and otp.plan_status_type_id <> 'OTA_COMPLETED'
and otpm.training_plan_member_id=p_training_plan_member_id;
SELECT otpm.training_plan_member_id
FROM ota_training_plan_members otpm
WHERE otpm.member_status_type_id <>'CANCELLED'
and otpm.member_status_type_id <>'OTA_COMPLETED'
and otpm.training_plan_id=csr_training_plan_id
and rownum=1;
CURSOR csr_tp_update(csr_training_plan_id number)
IS
SELECT otp.name,
otp.object_version_number,
otp.time_period_id,
otp.budget_currency
FROM ota_training_plans otp,
ota_training_plan_members otpm
WHERE otp.training_plan_id = csr_training_plan_id
AND otp.training_plan_id = otpm.training_plan_id
AND otpm.member_status_type_id = 'OTA_COMPLETED'
AND additional_member_flag = 'N';
OPEN csr_tp_update(rec1.training_plan_id);
FETCH csr_tp_update into l_name,l_object_version_number,l_time_period_id,l_budget_currency;
IF csr_tp_update%FOUND then
CLOSE csr_tp_update;
ota_tps_api.update_training_plan
(p_effective_date => sysdate
,p_training_plan_id => rec1.training_plan_id
,p_object_version_NUMBER => l_object_version_number
-- Modified to use OTA_COMPLETED
,p_plan_status_type_id => 'OTA_COMPLETED'
,p_name => l_name
,p_time_period_id => l_time_period_id
,p_budget_currency => l_budget_currency);
CLOSE csr_tp_update;
end Update_tp_tpc_change;
Procedure Update_tp_tpc_change
(p_training_plan_member_id ota_training_plan_members.training_plan_member_id%type)
is
CURSOR csr_tp_with_tpc
IS
SELECT otp.training_plan_id
, otp.plan_status_type_id
, otp.additional_member_flag
FROM ota_training_plans otp,
ota_training_plan_members otpm
WHERE otp.training_plan_id=otpm.training_plan_id
and otp.plan_status_type_id <> 'CANCELLED'
and otpm.training_plan_member_id=p_training_plan_member_id;
CURSOR csr_tp_update(csr_training_plan_id number)
IS
SELECT otp.name,
otp.object_version_number,
otp.time_period_id,
otp.budget_currency
FROM ota_training_plans otp
WHERE otp.training_plan_id = csr_training_plan_id;
OPEN csr_tp_update(rec1.training_plan_id);
FETCH csr_tp_update into l_name,l_object_version_number,l_time_period_id,l_budget_currency;
IF csr_tp_update%FOUND then
CLOSE csr_tp_update;
ota_tps_api.update_training_plan
(p_effective_date => sysdate
,p_training_plan_id => rec1.training_plan_id
,p_object_version_number => l_object_version_number
,p_plan_status_type_id => l_plan_status_type_id
,p_name => l_name
,p_time_period_id => l_time_period_id
,p_budget_currency => l_budget_currency);
CLOSE csr_tp_update;
END Update_tp_tpc_change;
Procedure Update_tp_tpc_change
(p_training_plan_member_id ota_training_plan_members.training_plan_member_id%type
,p_learning_path_ids OUT NOCOPY varchar2)
is
CURSOR csr_tp_with_tpc
IS
SELECT otp.training_plan_id
, otp.plan_status_type_id
, otp.additional_member_flag
, otp.learning_path_id
FROM ota_training_plans otp,
ota_training_plan_members otpm
WHERE otp.training_plan_id=otpm.training_plan_id
and otp.plan_status_type_id <> 'CANCELLED'
and otpm.training_plan_member_id=p_training_plan_member_id;
CURSOR csr_tp_update(csr_training_plan_id number)
IS
SELECT otp.name,
otp.object_version_number,
otp.time_period_id,
otp.budget_currency
FROM ota_training_plans otp
WHERE otp.training_plan_id = csr_training_plan_id;
OPEN csr_tp_update(rec1.training_plan_id);
FETCH csr_tp_update into l_name,l_object_version_number,l_time_period_id,l_budget_currency;
IF csr_tp_update%FOUND then
CLOSE csr_tp_update;
ota_tps_api.update_training_plan
(p_effective_date => sysdate
,p_training_plan_id => rec1.training_plan_id
,p_object_version_number => l_object_version_number
,p_plan_status_type_id => l_plan_status_type_id
,p_name => l_name
,p_time_period_id => l_time_period_id
,p_budget_currency => l_budget_currency);
CLOSE csr_tp_update;
END Update_tp_tpc_change;